VBA Macro: How to reference LastColumn variable in a simple formula

[mortgus]

GOAL: I have a report that will grow by one column of data each day. Each day, I need to add a formula into the first empty column that will find the difference between the value in the last column and another fixed column that will never change.

PROBLEM: I have successfully set my variable (LastColumn) and used it to select the first cell I want to add the formula into. But I do not know the proper syntax for using my LastColumn variable within my formula.

If I were to manually type the formula into cell R5, it would read:
ActiveCell.Formula = "=Q5-M5"

I need to know how to substitute the Q for LastColumn.

Then for example, the next day I'd have another date of data and would need to enter into cell S5:
ActiveCell.Formula = "=R5-M5" (with R being substituted for LastColumn)

Sorry if this is basic -- I'm very new to VBA and I've spent hours trying to figure this out.

 

[mikerickson]

Try

 ActiveCell.FormulaR1C1 = "=RC[-1]-RC13"