CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-365
stever
stever

Reputation: 13

Insert formula into cell including strings and variables

Good day I am trying to use VBA to insert the following formula into a Cell:

="last change completed: "& D2&" "&"by " &TEXT(E2;"dd-mmm-yy")

Where D2 would be a name and E2 would be a date. If i use the macro record i get:

Sub Macro8()
'
' Macro8 Macro
'

'
    Range("D12").Select
   ActiveCell.FormulaR1C1 = _
    "=""last change completed: ""& R[-11]C[-1]&"" ""&""on "" &TEXT(R[-11]C,""dd-mmm-yy"")"
   ' ActiveCell.FormulaR1C1 = _
        "=""last change completed: ""& R2C4&"" ""&""on "" &TEXT(R2C5,""dd-mmm-yy"")"
    Range("D13").Select
End Sub

The problem i have is the moment i try to change the cell locations to variables i get broken formulas in the cell, for example something similar to : 

="last change completed: "  &@ Range(SOP1)

where the "@range(SOP1) was supposed to be a variable cell location. i hope this is enough detail.

Upvotes: 1

Views: 661

Answers (1)

Kalicharan.N
Kalicharan.N

Reputation: 132

Please have a look at the below code. Hope this can solve your problem.

Change the sheets(1) value as needed(1- refers to 1st sheet in the workbook)

Sub Macro8()

rng1 = "D12"
rng2 = "D1"

ThisWorkbook.Sheets(1).Range("A1").Formula = "=""last knifehead change: """ & " & " & rng1 & " " & " & " & """ on """ & " & " & "Text(" & rng2 & ", ""dd-mmm-yy"")"

End Sub

Upvotes: 1

Related Questions