jvera524
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.
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.
ASKER
number of rows
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
You can change that to
1) Amend the formula to take the entire column into account. So the above becomes
2) Or this to take realistic rows into account.
Sid
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
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
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
Sid
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"),S heets("Yes terday").R ange($BM$2 :$BM$" & LastRow & ",1,False)"
Next
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(
For cl = 2 To cls.Row + 1
Cells(cl, "BP").Formula "=Vlookup(Cells(cl,"BM"),S
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sid