I created a VBA macro (with the help of others on here) and it works as it should on my computer. However, when I export the *BAS file to our server so others can import it onto their computer, when others attempt to execute the macro it comes back with a Runtime error 1004. When I go to debug, it highlights .FormulaR1C1 = sFrm. Here is the 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 Mar West 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 & _
I've reviewed the VBA code and it matches what I have on my personal workbook. Not sure what the issue is.
"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
Columns("J:J").Select
Selection.delete Shift:=xlToLeft
Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Rows.AutoFit
Range("H8").Select
End Sub
Bookmarks