VBA - Variable Range Vlookup

Harleykev

New Member
Joined
Apr 24, 2012
Messages
14
I need help with my code below.

I am copying from one workbook to another and pasting it in the first open cell (This part is working fine)

The Vlookups are also pasting in the right spot, but if the Vlookup is in Cell G45, I need the Vlookup to look at A45, currently it is hard coded at A2. How can I make that variable?

Second issue: I need to auto fill the vlookup cells to the bottom - Assuming this also needs to be variable.

Thank you for your input.


Private Sub Vlookup()

Dim RLReport As Variant
Dim LastRow As Integer
Dim EndLastRow As Integer

Workbooks("Destination").Worksheets("Data").Activate
LastRow = Range("A1").End(xlDown).Row

Workbooks.Open Filename:="Source A"
Windows("Source B").Activate
RLReport = Range("A15", Range("A15").End(xlDown).End(xlToRight)).Value

Workbooks("Destination").Worksheets("Data").Range("A" & LastRow + 1).Resize(UBound(RLReport, 1), UBound(RLReport, 2)).Value = RLReport
Workbooks("Destination").Worksheets("Data").Activate

Range("G" & LastRow + 1) = "TV Mounts"
Range("H" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"
Range("I" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,9,FALSE),""Not Traited"")"
Range("J" & LastRow + 1) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,10,FALSE),""Not Traited"")"

EndLastRow = Range("A1").End(xlDown).Row
' Range("g2:j2").AutoFill Destination:=Range("g2:j" & EndLastRow)
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
How about eg?

Rich (BB code):
Range("H2:H" & LastRow) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"
 

Harleykev

New Member
Joined
Apr 24, 2012
Messages
14
How about eg?

Rich (BB code):
Range("H2:H" & LastRow) = "=IFERROR(VLOOKUP(A2,'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"

It is the A2 that needs to be variable. It is pasting the Vlookup in the last row in the right column. Example it is pasting the Vlookup in H64 (if that is the last row) I need the vlookup to look in A64, but that is always changing.

I have multiple subs I am adding that will continue to add data below each one. There could be one Vlookup H2:H63 and a diferent one H64:H128 etc...

Hope that makes sense.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Like this?

Rich (BB code):
Range("H" & LastRow + 1) = "=IFERROR(VLOOKUP(A" & LastRow + 1 & ",'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"
 

Harleykev

New Member
Joined
Apr 24, 2012
Messages
14
Like this?

Rich (BB code):
Range("H" & LastRow + 1) = "=IFERROR(VLOOKUP(A" & LastRow + 1 & ",'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"

Yes exactly like that. Thank you very much.

Now, how do I auto fill from that spot to the bottom. The below is how I do it when I know the starting range:

Range("g2:j2").AutoFill Destination:=Range("g2:j" & EndLastRow)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it?

Rich (BB code):
Range("H" & LastRow + 1 & ":H" & EndlastRow) = "=IFERROR(VLOOKUP(A" & LastRow + 1 & ",'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"
 

Harleykev

New Member
Joined
Apr 24, 2012
Messages
14
Is it?

Rich (BB code):
Range("H" & LastRow + 1 & ":H" & EndlastRow) = "=IFERROR(VLOOKUP(A" & LastRow + 1 & ",'[Category Mod Changes - D72 - TV Wall Mounts.xlsx]TV Mounts'!$A:$J,8,FALSE),""Not Traited"")"

That worked... Thank you very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top