VlookUp giving random values instead of #N/A.

[green99]

I am using Vlookup to get values from another worksheet.
If there are corresponsding values present, the vba vlookup workks fine.However, I want #N/A to be printed when there is no match but instead of that any random value from the existing values gets printed.
Here is the code I am using:
On Error Resume Next

For i = 2 To 100

Object = sheet1.Cells(i, "C").Value
Range = sheet2.Range("C:I")

Value1 = Application.WorksheetFunction.VLookup(Object, Range, 7, False)

If IsError(Value1) Then

Sheet1.Cells(i, "H") = "#N/A"

Else

Sheet1.Cells(i, "H") = Value1

End If

Next

[Aflatoon]

Also cross-posted and answered here: https://www.excelforum.com/excel-programming-vba-macros/1207617-vlookup-giving-random-values-instead-of-n-a.html

[mdmackillop]

Use Find instead of a worksheet function

Sub Test()
On Error Resume Next
For i = 2 To 100
Object = Sheet1.Cells(i, "C").Value
Set Rng = Sheet2.Range("C:C")
Set Value1 = Rng.Find(Object, lookat:=xlWhole)
If Value1 Is Nothing Then
Sheet1.Cells(i, "H") = "#N/A"
Else
Sheet1.Cells(i, "H") = Value1.Offset(, 6)
End If
Next
End Sub

You can't use RANGE as a variable name and the function does not return a value if nothing is found: the previous value is retained.
This should work, but I don't like it
Sub Test2()
On Error Resume Next
For i = 2 To 100
Object = Sheet1.Cells(i, "C").Value
Set Rnge = Sheet2.Range("C:I")
Value1 = "#N/A"
Value1 = Application.WorksheetFunction.VLookup(Object, Rnge, 7, False)
Sheet1.Cells(i, "H") = Value1
Next
End Sub

[Aflatoon]

If you're going to use Find, you should really specify all its parameters, given that they persist between calls - including calls made from the UI. The code in the linked answer would work too.

[green99]

Use Find instead of a worksheet function

Sub Test()
On Error Resume Next
For i = 2 To 100
Object = Sheet1.Cells(i, "C").Value
Set Rng = Sheet2.Range("C:C")
Set Value1 = Rng.Find(Object, lookat:=xlWhole)
If Value1 Is Nothing Then
Sheet1.Cells(i, "H") = "#N/A"
Else
Sheet1.Cells(i, "H") = Value1.Offset(, 6)
End If
Next
End Sub


Thanks Aflaton

It worked by putting #N/A. I understand It's not a good practice but for the time being its good and please could you suggest me to make vlookup faster.

there are more 2k rows and it's taking around 40 min to run

You can't use RANGE as a variable name and the function does not return a value if nothing is found: the previous value is retained.
This should work, but I don't like it
Sub Test2()
On Error Resume Next
For i = 2 To 100
Object = Sheet1.Cells(i, "C").Value
Set Rnge = Sheet2.Range("C:I")
Value1 = "#N/A"
Value1 = Application.WorksheetFunction.VLookup(Object, Rnge, 7, False)
Sheet1.Cells(i, "H") = Value1
Next
End Sub

[mdmackillop]

It's unclear whom you are addressing in your response. Please include only specific lines in quotes pertinent to your response.
If VLOOKUP is slow, don't use it. FIND can do the same job.