Don't you like the answer I give you in your other thread Steve???
Sorry Steve
You add some things I see now after reading Tom's answer
Please stay in you old thread next time
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"STEVEB" wrote in message ...
I tried to delete rows in a MACRO that are returned as #N/A after
performing a VLOOKUP (The VLOOKUP MACRO works well). The MACRO I have
to delete #N/A rows is not working & I was hoping someone has any
suggestions. In my VLOOKUP the data is pasted as values, by doing so
is the #N/A not recognized as an error? Should I combine the two
MACROS into one? My VLOOKUP code & Delete N/A rows code is as follows:
VLOOKUP
Worksheets("Group 40").Activate
Dim rng As Range
With Worksheets("Group 40")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = _
"=VLOOKUP(A2," & _
"'[Reportable Accounts.xls]Sheet1'!" & _
"$A$2:$B$1147,1,FALSE)"
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
rng.Offset(0, 2).Formula = _
"=VLOOKUP(A2," & _
"'[Reportable Accounts.xls]Sheet1'!" & _
"$A$2:$B$1147,2,FALSE)"
rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value
End Sub
DELETE #N/A Rows
On Error Resume Next
ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _
EntireRow.Delete
On Error GoTo 0
End Sub
Thanks for your help
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/