Is there a Simple Way to code this.

[LutonBarry]

Folks, On the attached jpeg is a simple query. How would I code it to copy the formula in B2 down to B6. The number of rows will vary each time the the code is run.

[jolivanes]

Maybe

Sub Fill_It()
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B2:B" & lr).FillDown
End Sub

[nilem]

something like this

Sub ttt()
Dim SearchRange As Range
Set SearchRange = Range("G1:I12") 'just for example
With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    .FormulaR1C1 = "=VLOOKUP(RC[-1]," & SearchRange.Address(, , xlR1C1) & ",3,0)"
End With
End Sub

[LutonBarry]

Jolivanes, Thanks that works a treat, simple when you know how.
How does it work. Does the line "lr = Cells(Rows.Count, 1).End(xlUp).Row" Look right down col A to the very bottom to find the last cell with something enetered?

[LutonBarry]

Nilem, Thanks I guess your solution would work as long as the search range you specify would encompass the largest reasonable area you would expect a cell to be entered into.

[Aussiebear]

How does it work. Does the line "lr = Cells(Rows.Count, 1).End(xlUp).Row" Look right down col A to the very bottom to find the last cell with something enetered?

Yes it does because of the "rows.count, 1" and the .End(xlUp) requirements.

[LutonBarry]

Thanks.
So the .End(xlUp) tells it to go to the bottom and return back up to the first occupied cell?

[Bob Phillips]

No, the Rows.Count tells it to go to the bottom, the End(xlUp) tells it to then go up until it hits a cell with a value.

[LutonBarry]

xld, Got it now thanks.