I have a macro that has the following code in VBA and is executing as it should:
Sub test()
Dim sFrm As String
sFrm = "=IF(AND(RC[-9]=""4050.000"",RC[-7]>0),""Over Budget: Line item not budgeted, however late charges billed and collected per the Billing & Collection Policy.""," & vbLf & _
"IF(AND(RC[-9]=""4050.000"",RC[-7]<0),""Under Budget: Payment to xx for 50% of collected late fees from prior fiscal years.""," & vbLf & _
"IF(AND(RC[-9]="""",RC[-7]>1),""""," & vbLf & _
"IF(RC[-9]="""",""""," & vbLf & _
"IF(AND(RC[-5]>=100,RC[-4]>=""105.00%""),""Over Budget:""," & vbLf & _
"IF(RC[-4]=""100.00%"",""No Variance""," & vbLf & _
"IF(AND(RC[-7]<=0,RC[-6]>0),""Under Budget: No expense incurred""," & vbLf & _
"IF(AND(RC[-7]>0,RC[-6]=0),""Over Budget: Line item not budgeted""," & vbLf & _
"IF(AND(RC[-4]<""95.00%"",RC[-5]<=-100),""Under Budget:""," & vbLf & _
"IF(RC[-4]=""100.00%"",""No Variance"",(IF(AND(RC[-4]=""0.00%"",RC[-3]>1),""No Variance""," & vbLf & _
"IF(AND(OR(RC[-4]<""100.00%"",RC[-4]>""95.00%""),OR(RC[-5]>-100,RC[-5]<100)),""Under Budget: No significant variance"",""Over Budget: No significant variance"")))))))))))))"
With Range("J8:J187")
.FormulaR1C1 = sFrm
.Copy
End With
Range("H8").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
I had to make adjustment to the last line of the IF statement and when I run the macro, it gives me the Run time error. When I hit debug, it highlights in yellow .FormulaR1C1 = sFrm.
This is the code I am trying to run/execute (notice the change int he last line from the previous code):
Sub finalmacro()
Dim sFrm As String
sFrm = "=IF(AND(RC[-9]=""4050.000"",RC[-7]>0),""Over Budget: Line item not budgeted, however late charges billed and collected per the Billing & Collection Policy.""," & vbLf & _
"IF(AND(RC[-9]=""4050.000"",RC[-7]<0),""Under Budget: Payment to xx for 50% of collected late fees from prior fiscal years.""," & vbLf & _
"IF(AND(RC[-9]="""",RC[-7]>1),""""," & vbLf & _
"IF(RC[-9]="""",""""," & vbLf & _
"IF(AND(RC[-5]>=100,RC[-4]>=""105.00%""),""Over Budget:""," & vbLf & _
"IF(AND(RC[-7]<=0,RC[-6]>0),""Under Budget: No expense incurred""," & vbLf & _
"IF(AND(RC[-7]>0,RC[-6]=0),""Over Budget: Line item not budgeted""," & vbLf & _
"IF(AND(RC[-4]<""95.00%"",RC[-5]<=-100),""Under Budget:""," & vbLf & _
"IF(RC[-4]=""100.00%"",""No Variance"",(IF(AND(RC[-4]=""0.00%"",RC[-3]>1),""No Variance""," & vbLf & _
"IF(AND(OR(RC[-4]<""100.00%"",RC[-4]>""95.00%""),OR(RC[-5]>-100,RC[-5]<100)),""Under Budget: No significant variance""," & vbLf & _
"IF(AND(RC[-4]<""95.00%"",RC[-5]<0),""Under Budget: No significant Variance"",""Over Budget: No significant variance"")))))))))))))))))))))))"
With Range("J8:J187")
.FormulaR1C1 = sFrm
.Copy
End With
Range("H8").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
The formula that I trying to run is:
=IF(AND(A8="4050.000",C8>0),"Over Budget: Line item not budgeted, however late charges billed and collected per the Billing & Collection Policy.",(IF(AND(A8="4050.000",C8<0),"Under Budget: Payment to xx for 50% of collected late fees from prior fiscal years.",(IF(AND(A8="",C8>1),"",(IF(A8="","",(IF(AND(E8>=100,F8>="105.00%"),"Over Budget:",(IF(AND(C8<=0,D8>0),"Under Budget: No expense incurred",(IF(AND(C8>0,D8=0),"Over Budget: Line item not budgeted",(IF(AND(F8<"95.00%",E8<=-100),"Under Budget:",(IF(F8="100.00%","No Variance",(IF(AND(F8="0.00%",G8>1),"No Variance",(IF(AND(OR(F8<"100.00%",F8>"95.00%"),OR(E8>-100,E8<100)),"Under Budget: No significant variance",(IF(AND(F8<"95.00%",E8<0),"Under Budget: No significant Variance","Over Budget: No significant variance")))))))))))))))))))))))
and is pasted in cell J8.
I am not familiar with VBA or programming so please try to explain in laymens terms.
Thanks,
Bookmarks