VBA Vlookup Error_Please Help!

[kgallego]

Hello all,

Can anyone tell me what my error is? It stops on the line "ActCode = Application.Worksheetfunction.VLookup(day, Sheet2.Range("H8:H41"), Sheet2.Range("A8:A41"), True)"

This code is supposed to take numerical value "day", find the closest match (not exact) in Sheet2.Range("H8:H41"), and return the corresponding text (string) from Sheet2.Range("A8:A41") saved as ActCode.


Sub Filldata()

Dim ActCode As String
Dim day As Double
Dim TDHT As Double
TDHT = Sheet2.Cells(43, 8).Value

Dim i As Integer
For i = 1 To TDHT

Dim ActCode As String
Dim day As Double
day = Sheet3.Cells(10, 4 + i).Value
ActCode = Application.WorksheetFunction.VLookup(day, Sheet2.Range("H8:H41,A8:A41"), Sheet2.Range("A8:A41"), True)
Sheet3.Cells(11, 4 + i).Value = ActCode

Next i

End Sub



Any help would be greatly appreciated.

Thanks,
Kelsey

 

[Joe4]

In VLOOKUP, the column you are matching one needs to be the FIRST (left-most) column in your lookup range.
It looks like you are trying to match on column H and return a value from column A. VLOOKUP cannot do that.
Also, the third argument in VLOOKUP is a number, not a range. It is specifically the number of the column in your range that you want to return.
So, it your lookup range was B1:E10, and your wanted to return the from column E, this value would be 4, as column E is the fourth column in the range B1:E10.
It looks like you want to look left, so you will need to use something else, like INDEX/MATCH.
See this for tips on VLOOKUP: https://exceljet.net/things-you-should-know-about-vlookup

See here for an example of INDEX/MATCH: Left Lookup in Excel - EASY Excel Tutorial

 

[kgallego]

Thanks, I got it figured out.