VLookup erro

[Guest]

Hi

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

Sub Macro1(
Dim i As Intege
Dim a, b As Strin

Dim rangea, rangeb As rang

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


For i = 0 To 3
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
End Su


Run-time error '1004'

VLookup method of Range class faile

Can someone help me with this

 

[Don Guillett]

Without looking any further maybe too many iiiiii's in IF

--
Don Guillett
SalesAid Software
(e-mail address removed)

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))

 

[Dave Peterson]

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

 

[Dave Peterson]

There's an immediate if (iif) that works a lot like an If statement inside a
cell on a worksheet.

 

[J.E. McGimpsey]

Need to look a little farther, Don - take a look at the Iif function
in VBA Help.


I suspect the problem is using the WorksheetFunction object in

Application.WorksheetFunction.VLookup()

In some versions of XL, the WorksheetFunction object does not have
VLOOKUP as a valid property. Using

Application.VLookup(...)

instead works in all versions.

 

[Don Guillett]

Dave, I had forgotten about that.

 

[Don Guillett]

see ans to Dave.