Using VBA Variable in R1C1 Formula

[srmichael]

Please help!

I have this variable in my VBA code:

Dim lastColumn As Integer
lastColumn = ActiveSheet.Cells(7, Columns.Count).End(xlToLeft).Column

I have this formula as well:

ActiveCell.FormulaR1C1 = _
"=IF(RC[1]="""",1,IF(AND(RC[1]=1,LEFT(R[2]C,4)=LEFT(R[2]C[1],4)),1,IF(OR(LEFT(R[2]C,4)=LEFT(R7C30,4),R[2]C=VALUE(CONCATENATE(LEFT(R7C30,4)-1,RIGHT(R7C30,2)))),1,0)))"

Currently, lastColumn = 37 so I need the 30 in the formula to be lastColumn - 7 and to be absolute referenced. This lastColumn number will change each month. Next month it will be 38 and I will want the 30 to be 31, so lastColumn - 7 is what I need. How do I recode this formula so it works? I've tried many things.

Thanks

[PCI]

It is difficult without sample to do a test, perhaps try:
NOT TESTED
lastColumn = lastColumn - ActiveCell.column -7
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]="""",1,IF(AND(RC[1]=1,LEFT(R[2]C,4)=LEFT(R[2]C[1],4)),1,IF(OR(LEFT(R[2]C,4)=LEFT(R7C30,4),R[2]C=VALUE(CONCATENATE(LEFT(R7C30,4)-1,RIGHT(R7C" & lastColumn & ",2)))),1,0)))"

[Norie]

Which cell/column are you putting the formula in?