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

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


" VLOOKUP fORMULA" wrote:

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

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



" VLOOKUP fORMULA" wrote in
message ...
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" wrote:

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


" VLOOKUP fORMULA" wrote:

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

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

" VLOOKUP fORMULA" wrote:



"Jacob Skaria" wrote:

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


" VLOOKUP fORMULA" wrote:

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