VBA Vlookup Error_Please Help!

kgallego

Board Regular
Joined
Jul 26, 2011
Messages
80
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top