If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet

[ryguy7272]

Struggling with the code below:
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If cell.Value = "#N/A" Then
Worksheets("Summary Sheet").Cells(rw, "C") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

The code fails on this line. i guess there is not an equivalent of #N/A in
VBA. What do I need to use? By the way, the #N/A is the result of a
function; it is not hard-coded.

Thanks,
Ryan---

 

[CFS]

Try this:

Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("diego").Range("AB2:AB630")
rw = 1
For Each cell In rng
If IsError(cell) Then
Worksheets("Summary Sheet").Cells(rw, 3) = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

 

[Bernard Liengme]

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A1>0,A1, NA())
The trick is to have VBA know that you do not mean: does the cell have the
text "#N/A" ?
This user-defined function tell if mycell is displaying #N/A or not

Function trythis(mycell)
If Application.WorksheetFunction.IsNA(mycell) Then
trythis = "Has NA"
Else
trythis = "Not NA"
End If
End Function

best wishes

 

[ryguy7272]

Thanks for the look guys. CFS, it didn't work because the #N/A is the result
of a Vlookup(). It's because of what Bernard said. I guess VBA is looking
at the cell as if a user hit Alt+~. The #N/A is just the result of the
Vlookup function not finding a match. How can I modify the IF statement to
accommodate this?

Thanks,
Ryan---

 

[CFS]

I made a test using Vlookup() and the macro ran properly... The VBA function
IsError() can identify this kind of error.

 

[Bernard Liengme]

My UDF works when #N/A is the result of a VLOOKUP formula. Have you tried
it.
In A10 I have a VLOOKUP returning #N?A in B10 I have =trythis(A10) and it
returns "Has N/A"

Have you replaced the code in your macro: If cell.Value = "#N/A" Then
by : If Application.WorksheetFunction.IsNA(cell) Then

What does "CFS" refer to?

 

[ryguy7272]

Sorry for being so thick!! Overwhelmed with tasks today!!
I needed NOT!!!

If Not IsError(cell) Then

Works perfect, CFS and Bernard!


Thanks,
Ryan---

 

[Chip Pearson]

If Application.WorksheetFunction.IsNA(mycell) Then

Just for the record, another way is

If mycell.Value = CVErr(xlErrNA) Then
Debug.Print "N/A error"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ryguy7272]

Thanks Chip! You know, I Googled around a bit before posting. I saw a
sample that used your technique, but I couldn't get that working...thus the
post.

Thanks to everyone who participated!
Ryan---