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!

1 Answers

1
Tim Williams On Best Solutions

You didn't escape the other pair of quotes:

"=IF(OR(RC[-13]=""(blank)"",RC[-13]=""), ...

should be

"=IF(OR(RC[-13]=""(blank)"",RC[-13]=""""), ...