I am having an issue with a formula being placed onto my worksheet via vba. The formula interacts with data on a pivot table. When placed in cell Y8 on the worksheet the following formula functions as desired (non vba):
=IF(OR(L8="(blank)",L8=""),IF((K8-$A$2)/(365/12)<0,0,(K8-$A$2)/(365/12)),IF((L8-$A$2)/(365/12)<0,0,(L8-$A$2)/(365/12)))
The idea is to check if L8 is either null or (blank), if it is then use this formula: IF((K8-$A$2)/(365/12)<0,0,(K8-$A$2)/(365/12)). If L8 has a value (will be a date) then I want to use this slightly differnt formula: IF((L8-$A$2)/(365/12)<0,0,(L8-$A$2)/(365/12))).
Columns L and K are in a pivot table.
I used activecell.formulaR1C1 to translate my on sheet formula to R1C1. The only change I made was adding a set of quotation marks around "(blank)" --> ""(blank)"".
I am still getting a run-time 1004 message on my formula line of vba.
My VBA Code is here:
Sub PerformFormulas()
Dim LastRow As Long
LastRow = Worksheets("Calculator").Range("C" & Rows.Count).End(xlUp).Row
Worksheets("Calculator").Range("Y8:Y" & LastRow - 1).FormulaR1C1 = "=IF(OR(RC[-13]=""(blank)"",RC[-13]=""),IF((RC[-14]-R2C1)/(365/12)<0,0,(RC[-14]-R2C1)/(365/12)),IF((RC[-13]-R2C1)/(365/12)<0,0,(RC[-13]-R2C1)/(365/12)))"
End Sub
I checked that LastRow and Calculator are being recognized correctly and they are (I changed to a simple .select formula and that portion of the code works alright).
Thanks in advance for any help!
