VBA - Variable Range Vlookup

[Harleykev]

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)

 

[Andrew Poulsom]

How about eg?

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

 

[Harleykev]

How about eg?

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.

 

[royUK]

As you are using Excel 2007 or later then probably converting the data to a Table and making use of the Calculated Field function would eliminate the need for VBA.

Introduction To Excel Tables

 

[Andrew Poulsom]

Like this?

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]

Like this?

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]

Is it?

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]

Is it?

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.