Range formula copy

  • Thread starter Thread starter RE: VLOOKUP fORMULA
  • Start date Start date
R

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
 
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
 
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
 
Jacob Skaria said:
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


RE: VLOOKUP fORMULA said:
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
Click to expand...
Click to expand...


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

RE: VLOOKUP fORMULA said:
Jacob Skaria said:
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


RE: VLOOKUP fORMULA said:
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
Click to expand...
Click to expand...


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
Click to expand...
 
Back
Top