VBA to Fill Down with Dynamic Columns

[FrenchCelt]

Hello,

I'm writing a macro that will fill down a formula, but the column in question is dynamic, so I can't use the typical range that identifies the column by its letter designation. I have the code go so far as to insert the formula in the proper cell using a dynamic reference:

Range("A2").End(xlToRight).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=-1).Activate
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-50],'4-Week Data'!C[-52]:C[-51],2,FALSE)"

And from there, I'm at a loss on how to fill down the formula to the bottom of the column (which should be anchored based on what is in Column A, since that is guaranteed to always have data all the way to the bottom).

Does anyone have any suggestions?

 

[Fluff]

How about

Dim lr As Long
lr = Range("A" & rows.Count).End(xlUp).Row
Range("A2").End(xlToRight).Offset(1, 1).Resize(lr - 2).FormulaR1C1 = _
    "=VLOOKUP(RC[-50],'4-Week Data'!C[-52]:C[-51],2,FALSE)"

 

[FrenchCelt]

It places the formula and fills down two columns to the right of where I need it to go, but at least it goes down as far as I need it.

 

[Fluff]

Missed you had a minus sign in there, try

Dim lr As Long
lr = Range("A" & rows.Count).End(xlUp).Row
Range("A2").End(xlToRight).Offset(1, -1).Resize(lr - 2).FormulaR1C1 = _
    "=VLOOKUP(RC[-50],'4-Week Data'!C[-52]:C[-51],2,FALSE)"

 

[FrenchCelt]

Ok, now it works the way I need it, thanks!

 

[Fluff]

You're welcome & thanks for the feedback.