VBA VLookup should find value but doesn't

Question

I am trying to use VLookup in VBA and I am getting

'Unable to get the VLookup property of the worksheetfunction class' error.

I have read in other threads that this could be because the lookup value is not found. For my particular lookup, the values will always be found so this shouldn't be an issue. I have also sorted the lookup table in ascending order as that was another common issue. If I do the same vlookup in a cell it works without any problems

Sub VlookupIssues()

Dim lookUpValue As String
Dim result As Integer
Dim ws As Worksheet: Set ws = Sheets(3)
Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction

Sheet1.Activate
lookUpValue = Range("B1").Offset(2, 0).Value     'string "HR Manager"

Sheet3.Activate
result = wsFunc.VLookup(lookUpValue, ws.Range("A4:D42"), 3, False)

End sub

The lookup table looks like so (with some rows above/below):

Lookup Table

Does it work when you put the vlookup() function normally in a cell? — Dominique, Mar 05 '19 at 09:41
Have you tried the full range in the Vlookup, instead of `ws` like `worksheets("Sheetname").range("a2:d42")` instead of using `ws` assigned to `sheets(3)` — Nathan_Sav, Mar 05 '19 at 09:44
@Nathan_Sav - Yes, this works now (well it also gave an overflow error, but changing from integer to double solves it). Thank you very much! — JohntyR, Mar 05 '19 at 09:55
Sheet 3 can move around you see. `Set ws=worksheets("sheetname")` should resolve for you. — Nathan_Sav, Mar 05 '19 at 10:00

score 0 · Answer 1 · edited Mar 05 '19 at 10:14

0

Nathan's reply along with changing the result variable from integer to double did the trick

Have you tried the full range in the Vlookup, instead of ws like worksheets("Sheetname").range("a2:d42")

Instead of using ws assigned to sheets(3) set ws=worksheets("Sheetname") – Nathan_Sav

edited Mar 05 '19 at 10:14

Nathan_Sav

8,466
2
13
20

answered Mar 05 '19 at 09:56

JohntyR

5
2

score 0 · Answer 2 · answered Mar 05 '19 at 12:04

I set up the same and it ran fine for me. I'm running office 365. Suggest your problem could be with the text string itself you're looking up - like maybe an extra space that's not noticeable? Also vlookup is less elegant than using index and match functions which also don't require anything be sorted.

Try something like this perhaps: (you'll want to tailor it of course and I place the result somewhere which I additionally added a variable for the row which is set to 5)

Sub NewVLU()

Dim lookUpValue As String
Dim lRow As Long
On Error Resume Next
lookUpValue = Worksheets("Sheet1").Range("B1").Offset(2, 0).Value
lRow = Application.WorksheetFunction.Match(lookUpValue, 
Workbooks("test.xlsm").Sheets("Sheet3").Range("B:B"), 0)
result = Workbooks("test.xlsm").Sheets("Sheet3").Range("D" & lRow).Value
nRow = 5
Workbooks("test.xlsm").Sheets("Sheet1").Range("H" & nRow).Value = result
End Sub

VBA VLookup should find value but doesn't

2 Answers2