asked on

Automating VLookup

Hello,

I was able to record a macro which used a vlookup. The problem I am facing is that the range in the vlookup will change from day to day. How do I set and update the range for the vlookup formula when the macro is run? Please let me know if any information is needed. Thanks for the help.

SiddharthRout

Will the number of rows increase or the columns?

Sid

jvera524

ASKER

number of rows

SiddharthRout

Then it shouldn't be a problem.

Two ways to achieve what you want.

Here is an example

Let' s say the current VBA Code is

Sub Sample()
    Range("F2").Formula = "=Vlookup(C1,$C$1:$E$10,3,0)"
End Sub

Open in new window

You can change that to

1) Amend the formula to take the entire column into account. So the above becomes

Sub Sample()
    Range("F2").Formula = "=Vlookup(C1,C:E,3,0)"
End Sub

Open in new window

2) Or this to take realistic rows into account.

Sub Sample()
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
    Range("F2").Formula = "=Vlookup(C1,$C$1:$E$" & LastRow & ",3,0)"
End Sub

Open in new window

Sid

jvera524

ASKER

Below is some code. I am trying use what you gave above in a for loop. It gives a complie error on the bolded line Thank you for the help.

Dim cls As Range
Dim cl As Integer
Dim LastRow As Long

Sheets("Data").Select
Range("A2").Select
Set cls = Cells(Rows.Count, "A").End(xlUp)
LastRow = Sheets("Yesterday").Range("BM" & Rows.Count).End(xlUp).Row

For cl = 2 To cls.Row + 1
Cells(cl, "BP").Formula "=Vlookup(Cells(cl,"BM"),Sheets("Yesterday").Range($BM$2:$BM$" & LastRow & ",1,False)"
Next

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial