worksheetfunction vlookup

[irish1two3]

I have a macro that needs to do a vlookup. When I use the following code:

codem = Application.WorksheetFunction.VLookup(E10, (events), 8, False)

I get the error "Unable to get the VLookup property of the worksheetfunction class"

codem is a variable I want to assign the lookup value to so I can use it in other formulas.

Any ideas how to solve?

Thanks

[davesexcel]

Here's something that I have in one of my workbooks

Address = Application.WorksheetFunction.VLookup(Range("D13"), Sheets("Customer List").Range("A2:F1513"), 2, False)
Range("D14") = Address

[NBVC]

I had a similar problem and remember reading somewhere that an "On Error Resume Next" statement at the top of the code should eliminate that problem...

can't remember the reasoning behind it though...if you search the internet for that error, you may find it.

[sweep]

hi,

Is events a named range? If so replace with

range("events")

hth


dave

[irish1two3]

Thanks for all the input. I tried the changes suggested with no change in the error. I compared my code to that in Davesexcel's post and I don't see any difference. It almost seems as if my code doesn't recognize what to do with the vlookup. Is there a module that I have to ensure is loaded?

[NBVC]

did you try adding the error handling code I suggested.

Here is the place that explains it: http://www.cpearson.com/Excel/Callin...onsFromVBA.htm

Chip also explains that you can eliminate the Worksheetfunction propery....and simply use

codem = Application.VLookup(E10, (events), 8, False)

and here is another place that mentions it and also an alternative to Vlookup that might work better for you:

http://www.theofficeexperts.com/foru...hp/t-1062.html

[Dean England]

Hey Irish,

I had an instance several months ago along the same lines and after some extensive research found out the the Worksheet Function will not work or will at best be very flaky in a search or match situation. The best alternative I found was to use the Application.Vlookup option. Worked fine for me.

Dean

[irish1two3]

Yes, I put in the error handling you suggested and I got the same error until I eliminated the "Worksheetfunction" from the code. Once I did that, my variable was set to an error value as if it did not find the data in the range. The problem is, that if I use VBA to grab a cell and set its' value to equal that lookup formula, it works and finds the data I am looking for.

So, the formula works and finds the correct data if I plug the formula into a cess. If I set a variable to equal that lookup, it doesn't fine the data??? I would like to try the .Find, but I am fairly clueless as to how that works. I have read all of the examples, but I don't understand how it's working. Can someone explain what this code (from the example) is doing?

Sub test()
Dim cl As Range, n As Range, mYStRow As Integer
Dim y(1 To 199), z As Integer
mYStRow = 2
For Each cl In Sheets("Percentiles").Range("H2:H200")
On Error Resume Next
For z = LBound(y) To UBound(y)
If y(z)(o) = cl(, -1) Then
mYStRow = y(z)(1) + 1: End If
Next
On Error GoTo 0
Set n = Sheets("DB_Import").Range("f" & mYStRow - 1 _
& ":f200").Find(cl(, -1))
If Not n Is Nothing Then
cl = n(, 14)
y(cl.Row - 1) = Array(n, n.Row)
Else: cl = "0"
End If
mYStRow = 2
Next
End Sub