VLOOKUP Fill Right for Multiple Rows and Dynamic Range of Columns

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
101
Hello,

I'm in the process of making a macro and I'm stuck at a section where I've done a VLOOKUP and filldown several rows, and the next part is to fill right all of those rows at once to an indeterminate number of columns to the right (my VLOOKUP formula is set up to adjust accordingly as it gets filled to the right down all the rows--it works fine if I do it manually). I know how to fill right for one row when the column number is dynamic, but doing it for rows B41:B54 (these are fixed and will always be the same starting point) to whichever column where the data ends has proven tricky.

This is what I have so far to get to where I'm stuck:

VBA Code:
Range("B41").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"
Selection.AutoFill Destination:=Range("B41:B54"), Type:=xlFillDefault

I have this set up earlier in my code to dynamically fill right something else:

VBA Code:
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B40:B" & lastrow).AutoFill Destination:=Range(Cells(40, "B"), Cells(lastrow, lastcol)), Type:=xlFillDefault

So I wanted to try something similar with a range of B41:B54 to fill right, but nothing I tried has worked.

Does anyone have any suggestions?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B41", Cells(lastrow, lastcol)).FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"
 

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
101
That didn't work. It filled to the right in Row 41 with this:

=VLOOKUP($A41,$3:$16,COLUMN(#REF!),FALSE) in B41

And wiped out the data I had in Row 40, replacing with this:

=VLOOKUP($A40,$3:$16,COLUMN(B1048576),FALSE) in B40
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
That suggests that row 40 is the last row with data in col B.
If you want it on rows 41:54 regardless of last row, try
VBA Code:
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
Range("B41", Cells(54, lastcol)).FormulaR1C1 = "=VLOOKUP(RC1,R3:R16,COLUMN(R[-40]C),FALSE)"
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,229
Messages
5,546,632
Members
410,751
Latest member
Mike Davis 1977
Top