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


"ryguy7272" wrote:

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---

--
RyGuy


"Bernard Liengme" wrote:

My guess: a cell my DISPLAY #N/A but it may STORE a formula such as
=IF(A10,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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
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---


--
RyGuy