PDA

View Full Version : [SOLVED] Is there a Simple Way to code this.



LutonBarry
11-19-2015, 06:36 PM
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
11-19-2015, 10:28 PM
Maybe

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

nilem
11-19-2015, 10:28 PM
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
11-20-2015, 12:31 AM
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
11-20-2015, 12:35 AM
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
11-20-2015, 04:05 AM
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
11-20-2015, 07:08 AM
Thanks.
So the .End(xlUp) tells it to go to the bottom and return back up to the first occupied cell?

Bob Phillips
11-20-2015, 07:26 AM
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
11-21-2015, 01:43 AM
xld, Got it now thanks.