Inserting a formula in a cell with VBA

[scoha]

I have a line where I want to create a formula in a cell as part of my macro and have developed this line.

Its meant to insert =L&i -J&i into the cell iM, where "i" is a row counter.

Sheets("ClosedIssues").Cells(i, "M").Formula = "=""L""&i-""J""&i"

But for some reason this isnt working so was hoping someone could point out the error?

 

[shg]

Use an R1C1 formula:

Worksheets("ClosedIssues").Cells(i, "M").FormulaR1C1 = "=RC[-1]-RC[-3]"

EDIT: If you wanted to use an A1 formula,

Worksheets("ClosedIssues").Cells(i, "M").Formula = "=L" & i & " - J" & i

 

[scoha]

Great - thanks shg - forgot about R1C1 terminology

 

[shg]

You're welcome, glad it helped.