PDA

View Full Version : Help on #N/A error



gvreddyhr
08-12-2010, 09:34 AM
Hi ,
I have a clarification regarding the coding for vlookup in excel VBA, I would request anyone to help me by giving the solution, below is the code

Sub Vlookup()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("D1:D" & lastrow).Formula = Application.WorksheetFunction.VLookup(Range("C1:c20000"), Range("A1:B20000"), 2, False)
End Sub
The above code works perfectly, but if lookup value is not found in range then I will be getting “#N/A” error, if lookup value is not in the range i want cell to left in blank.
kindly help me.
thanks in advance.

Regards
GV Reddy

p45cal
08-12-2010, 11:32 AM
Sub Vlookup()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("D1:D" & lastrow)
.Formula = Application.WorksheetFunction.Vlookup(Range("C1:c20000"), Range("A1:B20000"), 2, False)
.SpecialCells(xlCellTypeConstants, 16).ClearContents
End With
End Sub

gvreddyhr
08-12-2010, 08:58 PM
Sub Vlookup()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("D1:D" & lastrow)
.Formula = Application.WorksheetFunction.Vlookup(Range("C1:c20000"), Range("A1:B20000"), 2, False)
.SpecialCells(xlCellTypeConstants, 16).ClearContents
End With
End Sub


Thank you so much for the post, It work's perfect, but how is this code working (SpecialCells(xlCellTypeConstants, 16).ClearContents) and wht does number "16" states.

Regards
GV Reddy

geekgirlau
08-12-2010, 10:19 PM
That's an interesting concept P45cal - I normally would use something like


Range("D1:D" & lastrow).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-1], C1:C2, 2, FALSE)),"""",VLOOKUP(RC[-1], C1:C2, 2, FALSE))"

p45cal
08-13-2010, 03:21 AM
gvreddyhr, that line is the similar to selecting some cells, pressing F5, clicking Special, choosing Constants and unchecking all but the Errors checkbox, clicking OK then the delete key.
Check out help by pressing F1 while on the word SpecialCells
(xlErrors enumerates to 16).

geekgirlau, I too would have done that (then if, like gvreddyhr, I wanted just values rather than a formula to remain in the cells, I'd have done a .value=.value).
gvreddy's solution seems quite elegant, and has the advantage that it won't trip up if the range is large and the sheet takes significant time to recalculate before you use the .value of the same cells shortly afterwards in the code (as has happened to me).