FrenchCelt
Board Regular
- Joined
- May 22, 2018
- Messages
- 214
- Office Version
- 365
- Platform
- Windows
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:
I have this set up earlier in my code to dynamically fill right something else:
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?
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?