Range formula copy

[RE: VLOOKUP fORMULA]

Any body please help....

Is there any way to place the below mentioned formula from the second cell
(second row) of the range “RM1†instead of the first row?



Range("RM1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3))"
Range("RM1").FillDown

Thanks in advance

 

[Jacob Skaria]

Try the below. You dont need to select the cell

Range("RM2").FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3))"
Range("RM2:RM" & Cells(Rows.Count, "RK").End(xlUp).Row).FillDown

PS: left(cellreference,3) is enough

If this post helps click Yes

 

[Tim Williams]

Assuming it's a single-column contiguous range

Sub Tester()
With Range("RM1")
.Cells(2).FormulaR1C1 = _
"=CONCATENATE(LEFT(RC[-2],3))"
Range(.Cells(2), .Cells(.Cells.Count)).FillDown
End With
End Sub

Tim

 

[RE: VLOOKUP fORMULA]

Try the below. You dont need to select the cell

Range("RM2").FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3))"
Range("RM2:RM" & Cells(Rows.Count, "RK").End(xlUp).Row).FillDown

PS: left(cellreference,3) is enough

If this post helps click Yes
---------------
Jacob Skaria

Any body please help....

Is there any way to place the below mentioned formula from the second cell
(second row) of the range “RM1†instead of the first row?



Range("RM1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3))"
Range("RM1").FillDown

Thanks in advance

The above formula given is not working, let me explain again.

My range name is “Room1†(A1:A10)
The result is in (B1:B10) it is working fine with the below code.
My request was I want to keep B1 blank without any formula and fill the
formula from B2:B10.

Range("Room1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-1],3))"
Range("Room1").FillDown

 

[Trevor Williams]

This will do what you're after

Range("Room1").Offset(1, 0).Resize(Range("Room1").Rows.Count - 1,
1).FormulaR1C1 = "=LEFT(RC[-1],3)"


not sure why you're using CONCATENATE as you're only referencing 1 thing,
but you can always add it back in if I've missed the idea!

HTH

Trevor Williams

Try the below. You dont need to select the cell

Range("RM2").FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3))"
Range("RM2:RM" & Cells(Rows.Count, "RK").End(xlUp).Row).FillDown

PS: left(cellreference,3) is enough

If this post helps click Yes
---------------
Jacob Skaria

Any body please help....

Is there any way to place the below mentioned formula from the second cell
(second row) of the range “RM1†instead of the first row?



Range("RM1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-2],3))"
Range("RM1").FillDown

Thanks in advance

The above formula given is not working, let me explain again.

My range name is “Room1†(A1:A10)
The result is in (B1:B10) it is working fine with the below code.
My request was I want to keep B1 blank without any formula and fill the
formula from B2:B10.

Range("Room1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(RC[-1],3))"
Range("Room1").FillDown