Unable to get the vlookup property of the worksheet function class

[SBF12345]

I am receiving the "unable to get the vlookup property of the worksheet fuction class" error on the line below.

Set RngStart = Application.WorksheetFunction.VLookup(StartDate, SearchArray, 2, False)

The variable StartDate and SearchArray are constructed with the code below.

StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value

With ActiveWorkbook.Sheets("ActiveSheet")

    EndArray = .Cells(Rows.Count, 6).End(xlUp).Row


    Set SearchArray = .Range("E6", .Cells(EndArray, "F"))


End With

Any ideas as to why I might be receiving this error?


**unrelated

I shouldn't use the worksheet name "ActiveSheet" because of the object Activesheet, but i did in this case, apologies, just a heads up.

 

[Kamolga]

Hi,

I tend to avoid vlookup in vba, Find does the job quicker (I would reduce the SearchArray to 1 column) and I offset to get the value.
Note that I set up aa=1 to put my testing value on AllDistanceMeasures sheet

Sub Search()
Dim EndArray As Long
Dim SearchArray As Range
With ActiveWorkbook.Sheets("ActiveSheet")
    EndArray = .Cells(Rows.Count, 6).End(xlUp).Row
    Set SearchArray = .Range("E6", .Cells(EndArray, "F"))
End With
Dim StartDate As Date
Dim aa As Long
[COLOR=#ff0000]aa = 1[/COLOR]
StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).Value


Dim RngStart As Range
[COLOR=#0000ff]Set RngStart = SearchArray.Find(StartDate).Offset(0, 1)[/COLOR]


MsgBox RngStart.Value
End Sub

To get it to work, I had to dim StartDate as a date, not a string as I entered a date in excel.

 

[SBF12345]

interesting...it runs, but I am running into the same problem as I was when I had previously tried to use find to set addresss. (motivation for trying to write the same functionality with vlookup). 4 of 5 times I have date values that begin with "02". The find method returns values associated with a date value "12". So for instance the date "02/10/10" is returning the date "12/10/10". Any ideas or work arounds?

 

[Kamolga]

interesting...it runs, but I am running into the same problem as I was when I had previously tried to use find to set addresss. (motivation for trying to write the same functionality with vlookup). 4 of 5 times I have date values that begin with "02". The find method returns values associated with a date value "12". So for instance the date "02/10/10" is returning the date "12/10/10". Any ideas or work arounds?

Wait, how can it mix 02 and 12? Even something doing with date format (I believe VBA works the american way mm/dd/yyyy) can't explain it. On my test it get right.
To be sure, can you go to an empty column next to search array, type = then select the cell aa,9 in distance measure sheet (put dollars around it), then = again and select the date cell same row in the array. When you dragg it down, you should have false every time and true for only match or does it also say true for 12 and 02?

 

[RoryA]

I’ve never seen that behaviour either.

The original error meant that the lookup value wasn’t found. (Also note that vlookup doesn’t return a range object, so the Set statement would fail even if the value were found)

 

[SBF12345]

an easy fix find with xlwhole not xlpart!