VLOOKUP in VBA

[ActuaryToBe]

I am having a problem with the following line of code in VBA:

If Application.WorksheetFunction.VLookup(Cells(i, 26), Range(Cells(4, 3), Cells(lastRCap, 3)), 1, False) = "123" Then

I am getting an error message that says: "Application-defined or object-defined error". Could somebody help me out; I am somewhat of a beginner in using VBA.

[Tyger]

Try using "cell" instead of "cells".

Unless that was just a typo.

[Old Timer]

What is the value of lastRcap? Is there really a match? Is the matched value really text or is it numeric?

[MathGuy]

I ran this snippet of code through VBA and it worked just fine. However, you get the error you've described if the variables i and lastRCap are not defined.

[ActuaryToBe]

lastRCap is the # of rows in the list, and yes, there is really a match that is numeric.

I don't know if it matters, but it is in a for loop, I tried changing Cells to 'Cell' as per Tyger's suggestion but that just gave a compile error.

Here is the whole bit of code I'm using it in:

For i = 2 To lastRSource
If Application.WorksheetFunction.VLookup(Cells(i, 26), Range(Cells(4, 3), Cells(lastRCap, 3)), 1, False) = "123" Then
Range("A1") = 123
End If
Next i

[ActuaryToBe]

Hmm..I don't think I changed anything, but when I run it now, I am getting an error saying: "Unable to get the VLookup property of the WorksheetFunction class"

[ActuaryToBe]

Sorry, ignore the last post about the "Unable to get the VLookup property of the WorksheetFunction class" error; that was a result of something I commented out by mistake.

[whisper]

Do you have the Analysis Toolpack VBA addin installed?

[ActuaryToBe]

Originally Posted by whisper

Do you have the Analysis Toolpack VBA addin installed?

Yes, I do.

[whisper]

You need to put in some error handling procedures.

The problem you're getting is when the value you're looking up is not in the vlookup range.

You need an On Error routine.