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).
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.