VBA VLookup Failing

[cmf30]

Hi,

I am very much a beginner at VBA and I am trying to use a VLookup and it keeps erroring and (I am not sure why). I have 2 sheets one called COMBINED and the other called HRIS.
1) I want to take a Staff ID in COMBINED tab and find the corresponding ID in the HRIS file
2) Check the nationality for that person (which is in a different column of HRIS tab)
3) Once done, I want to update the Nationality column in the COMBINED File if the existing value is blank

The values I am looking for do exist in the HRIS tab.

I am using a VLookup to do this, but at the moment it keeps failing to get Run Error 1004 or Error Method Vlookup of Object Worksheet Function failed.

The relevant lines of the code is as follows:

strNationality = Application.WorksheetFunction.VLookup(strStaffID, Sheets("HRIS").Range("G2:Z500"), 10, False)
 
If Sheets("COMBINED").Range("S" & i).Value = "" Then

Sheets("COMBINED").Range("S" & i).Value = strNationality

Else

End If

 

[steve the fish]

What is strStaffID?

 

[cmf30]

Hi Steve,

Thanks for getting back

Sorry, is the StaffID that is being searched from It is in COMBINED tab.

The Staff ID is present in both tabs and so that is why I am using it to compare. But I am wondering if there is an issue with the formatting because I just did the below check and the two appear to be a different format. One seems to be a string and the other a number and so I am guessing the Vlookup doesn't see them as equal (below example, both CombinedL2 and HRISG51 show the same staff ID). Per below, I did try to convert the column formats beforehand but the two formats are still coming out as different.

Worksheets("HRIS").Columns("G").NumberFormat = "0"
Worksheets("COMBINED").Columns("L").NumberFormat = "0"

MsgBox VarType(Sheets("COMBINED").Range("L2"))
MsgBox VarType(Sheets("HRIS").Range("G51"))

Kind regards,
Claire

 

[steve the fish]

Thats almost certainly why it isnt working. Is it numeric? I still cant see where you are assigning it.

 

[steve the fish]

It probably is im guessing. Use

Dim strStaffID as Long

and see if that helps.

 

[cmf30]

Thanks a million, Steve, that worked! Really appreciate it.