Is there a Simple Way to code this.

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.

Maybe

Code:

---

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

---

something like this

Code:

---

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

---

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?

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.

Quote:

---

Originally Posted by LutonBarry

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.

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

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.

xld, Got it now thanks.