Code:
Sub PreAudit()
Dim ptc As Range
Dim ften As Range
Dim dollars As Range
Dim dollarsr As Range
Dim ascalcr As Range
Dim asvarr As Range
Set ptc = Range("A1:AZ1").Find("PreTenCont").Offset(1, 0)
Set ften = Range("A1:AZ1").Find("fte num").Offset(1, 0)
Set dollars = Range("A1:AZ1").Find("Dollars").Offset(1, 0)
Set dollarsr = Range(dollars, dollars.End(xlDown))
Set ascalcr = dollarsr.Offset(0, 1)
Set asvarr = dollarsr.Offset(0, 2)
Debug.Print ptc.Address
Debug.Print ften.Address
Debug.Print dollars.Address
Debug.Print dollarsr.Address
Debug.Print ascalcr.Address
Debug.Print asvarr.Address
Range("A1:AZ1").Find("Dollars").Select
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "A&S Calculation"
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "A&S Variance"
ActiveCell.Offset(1, -1).Select
ActiveCell.Formula = "=IF(" & ptc.Address(RowAbsolute:=False) & "=" & Chr(34) & "PRE" & Chr(34) & ",IF(" & ften.Address(RowAbsolute:=False) & ">=100," & Chr(34) & "2000" & Chr(34) & ", IF(" & ften.Address(RowAbsolute:=False) & ">=50," & Chr(34) & "1600" & Chr(34) & ", IF(" & ften.Address(RowAbsolute:=False) & ">=25," & Chr(34) & "1000" & Chr(34) & "," & Chr(34) & "0" & Chr(34) & "))),IF(" & ften.Address(RowAbsolute:=False) & ">=100," & Chr(34) & "1700" & Chr(34) & ", IF(" & ften.Address(RowAbsolute:=False) & ">=50," & Chr(34) & "1360" & Chr(34) & ", IF(" & ften.Address(RowAbsolute:=False) & ">=25," & Chr(34) & "850" & Chr(34) & "," & Chr(34) & "0" & Chr(34) & "))))"
Selection.AutoFill Destination:=ascalcr, Type:=xlFillDefault
Range("A1:AZ1").Find("A&S Variance").Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=R[0]C[-1]-R[0]C[-2]"
Selection.AutoFill Destination:=asvarr, Type:=xlFillDefault
End Sub
The last line and the 4th last line is giving me error 1004. Debug.Print shows the following:
$J$2
$H$2
$L$2
$L$2:$L$984
$M$2:$M$984
$N$2:$N$984
When the formula is written in the cell it appears as: =IF($J2="PRE",IF($H2>=100,"2000", IF($H2>=50,"1600", IF($H2>=25,"1000","0"))),IF($H2>=100,"1700", IF($H2>=50,"1360", IF($H2>=25,"850","0"))))
Could i be having this problem because of my formula? or is something else the problem?
Is it because I'm using Selection?
Everything else works, I just need to autofill the 2 formulas.