ExcelBanter - VLookup erro

Try getting rid of .worksheetfunction

Used like application.worksheetfunction.vlookup(), if no match is found, then an
error is raised:

dim Res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.num < 0 then
'no match found
err.clear
end if
on error goto 0

But if you use it like: application.vlookup(), an error can be returned:

dim res as variant
res = application.vlookup(...)
if iserror(res) then
'no match found
end if

I find the second version easier.

In fact, instead of having to do the vlookup twice, you may want to rewrite your
routine slightly.

Option Explicit

Sub Macro1()
Dim i As Integer
Dim a, b As String
Dim res As Variant

Dim rangea As Range
Dim rangeb As Range

Set rangea = Range(Cells(2, 4), Cells(20, 5))
Set rangeb = Range(Cells(2, 8), Cells(20, 8))

For i = 0 To 30
a = Cells(2 + i, 8)
res = Application.VLookup(a, rangea, 2, False)
If IsError(res) Then
Cells(2 + i, 9).Value = 1
Else
Cells(2 + i, 9).Value = res
End If
Next i
End Sub

or:

Option Explicit

Sub Macro1()
Dim i As Integer
Dim a, b As String
Dim res As Variant

Dim rangea As Range
Dim rangeb As Range

Set rangea = Range(Cells(2, 4), Cells(20, 5))
Set rangeb = Range(Cells(2, 8), Cells(20, 8))

For i = 0 To 30
a = Cells(2 + i, 8)
On Error Resume Next
res = Application.WorksheetFunction.VLookup(a, rangea, 2, False)
If Err.Number < 0 Then
Cells(2 + i, 9).Value = 1
Err.Clear
Else
Cells(2 + i, 9).Value = res
End If
On Error GoTo 0
Next i
End Sub

And one more thing:

Watchout for this:

Dim rangea, rangeb As range

This actually declares rangeb as a range, but rangea is declared as a Variant.

dim rangea as range, rangeb as range
or
Dim rangea As Range
Dim rangeb As Range

would be better

Miguel wrote:

Hi,

I use VLookp in lines of the programmig however, the code is get same error.
Option Explicit
4 5 8 9
d0 n1 d0 n1
d1 n2 d1 n2
d3 n3 d3 n3
d4 n4 d4 n4
d5 n5 d20 erro
d6 n6
d7 n7

Sub Macro1()
Dim i As Integer
Dim a, b As String

Dim rangea, rangeb As range

Set rangea = range(Cells(2, 4), Cells(20, 5))
Set rangeb = range(Cells(2, 8), Cells(20, 8))

For i = 0 To 30
a = Cells(2 + i, 8)
Cells(2 + i, 9) = iif(IsError(Application.WorksheetFunction.VLookup( a, rangea, 2, False)), 1,_ Application.WorksheetFunction.VLookup(a, rangea, 2, False))
Next i
End Sub

Run-time error '1004':

VLookup method of Range class failed

Can someone help me with this?

--

Dave Peterson