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