VBA VLOOKUP to return a value in a specific cell

[Ughes]

Hi All,

I am fairly new to the whole VBA thing and I am taking my first baby steps into Worksheet Functions.

The order of the day is vlookup. I have written the code below for my Vlookup but cannot figure out how to have the vlookup return the value from the "InitialRange" range into a specific cell.

All the lookup values are in column D and the referenced range "InitialRange" is in separate worksheet.

If i typed the normal (none VBA) lookup formula into the cells in column C, it would return the value. Need to do the same in VBA.

Thanks for all the help!


Sub GetCountry()

Dim Res As Variant
Dim CountryRange As Range
Dim InitialRange As Range

On Error Resume Next
Err.Clear

Set CountryRange = Range("D5:D50000")
Set InitialRange = Sheets("Country Code").Range("E2:F116")

Res = Application.WorksheetFunction.VLookup(CountryRange, InitialRange, 2, False).Value = ("C5")

If Err.Number = 0 Then

Else

End If


End Sub

 

[Jonmo1]

Welcome to the board...

1st bit of advice is to drop the worksheetfunction, and just use application by itself..

When using worksheetfunction, and the vlookup is #N/A, it get's a debug error.
Hence the need for On Error Resume Next.

But if you drop the WorksheetFunction, and just use Application, then it returns the error value, but it does not debug...

So you assign the result of the Application.Vlookup to a variable.
Then you can test the variable for error...
If it's not an error, put the value of the variable into a Cell.
If it IS an error, do nothing.


Also, you can't have a multicell range in the first argument of the vlookup.
You need to loop through the CountryRange and do the vlookup on each value in that range...


Try something like

Sub GetCountry()
Dim Res As Variant
Dim CountryRange As Range, InitialRange As Range, C As Range
Set CountryRange = Range("D5:D50000")
Set InitialRange = Sheets("Country Code").Range("E2:F116")
For Each C In CountryRange
    Res = Application.VLookup(C, InitialRange, 2, False)
    If Not IsError(Res) Then
        C.Offset(0, -1).Value = Res
    End If
Next C
End Sub

 

[Ughes]

Jonmo1

Thank you so much for the super fast response. Worked like a charm!

My first time using the board and I am one satisfied customer.

All the best
Ughes

 

[Jonmo1]

Glad to help, thanks for the feedback.