Insert Formula to cell next to active cell

[Truiz]

Good Morning,

I'm trying to come up with a code to insert a formula in the column next to the active cell

for example if active cell is A2 i want to insert the formula in B2 and then another one to cell C2

The formulas themselves have references to the active cell such as LEFT(A2,FIND(" ",A2)-1) since not all the time the active cell is going to be A2 how could I accomplish this?

does ActiveCell.Offset(RowOffset:=0, ColumnOffset:=+1).InsertFormula = LEFT(ActiveCell,FIND(" ",ActiveCell)-1) sound like a possible thing?

Regards,

 

[wideboydixon]

I'd be inclined to use R1C1 format in this case:

ActiveCell.Offset(0,1).FormulaR1C1 = "=LEFT(RC[-1],FIND("" "",RC[-1])-1)"

WBD

 

[Truiz]

I'm getting Error 1004

 

[Kenneth Hobson]

Obviously, you will get errors if the formula does not handle them.

Sub F2R()
  Dim s As String
  With ActiveCell
    If .Column + 3 > Columns.Count Then Exit Sub
    'LEFT(A2,FIND(" ",A2)-1)
    s = "=LEFT(" & .Address(False, False) & ",FIND(" & _
      """ ""," & .Address(False, False) & ")-1)"
    .Offset(, 1).Formula = s
    .Offset(, 2).Formula = s
  End With
End Sub

 

[Truiz]

Thank you Kenneth