autofill method of range class failed

[jkicker]

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.

 

[JLGWhiz]

Selection.AutoFill Destination:=asvarr, Type:=xlFillDefault

If the variable asvarr does not include the range 'Selection', you will get the error. The FillDown range must include the source range.

 

[jkicker]

Selection.AutoFill Destination:=asvarr, Type:=xlFillDefault

If the variable asvarr does not include the range 'Selection', you will get the error. The FillDown range must include the source range.

'
'Selection' for that case is the cell where the second formula was written, which is N2, which is included in asvarr. In the previous formula 'Selection' is M2, which is included in ascalcr. The FillDown range does include the source range, as far as i can tell.

 

[JLGWhiz]

I don't see anything else in the code, but it is difficult to trouble shoot without the worksheet. Maybe you could get a clue if you step through the code using the F8 key. Also, the 1004 error is usually related to the command statement (FillDown in this case) syntax or clerical errors such as omissions and misspelled words. Since your formulas are apparently being posted to the cells correctly, I don't believe they would produce a 1004 error. They could be the cause if they are not properly configured, but then the error would be triggered on the line where the formulas are posted to the sheet. That is about all I can offer at this point.

 

[Michael M]

I d also check the formula, as it has contradictions....

=IF($J2="PRE",IF($H2>=100,"2000", [color=red]IF($H2>=50,"1600"[/color], IF($H2>=25,"1000","0"))),IF($H2>=100,"1700", [color=red]IF($H2>=50,"1360"[/color], IF($H2>=25,"850","0"))))

 

[jkicker]

I d also check the formula, as it has contradictions....

=IF($J2="PRE",IF($H2>=100,"2000", [COLOR=red]IF($H2>=50,"1600"[/COLOR], IF($H2>=25,"1000","0"))),IF($H2>=100,"1700", [COLOR=red]IF($H2>=50,"1360"[/COLOR], IF($H2>=25,"850","0"))))

They are in different arguments. The first one is the argument if PRE is true, the second if it's false.

 

[jkicker]

I don't see anything else in the code, but it is difficult to trouble shoot without the worksheet. Maybe you could get a clue if you step through the code using the F8 key. Also, the 1004 error is usually related to the command statement (FillDown in this case) syntax or clerical errors such as omissions and misspelled words. Since your formulas are apparently being posted to the cells correctly, I don't believe they would produce a 1004 error. They could be the cause if they are not properly configured, but then the error would be triggered on the line where the formulas are posted to the sheet. That is about all I can offer at this point.

The worksheet is a table with 984 rows by 48 columns with no blanks in the Dollars columns which i'm using to set the ranges.

    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)

...what i was going to say next is i then insert 2 columns next to the Dollars column, where i put my formula. but i just realized that i already defined the ranges to be those columns before i inserted new ones. so my range no longer contains M2, because VBA adjusts my variable?
and i just tested my theory by Debug.Print ascalcr after rows are inserted. I get the error 91: Object variable or With block variable not set. I'm going to reorganize my sub and repost it as a solution.

 

[jkicker]

Sub PreAudit()
    
Dim dollars As Range
Dim dollarsr As Range
    
    Set dollars = Range("A1:AZ1").Find("Dollars").Offset(1, 0)
    Set dollarsr = Range(dollars, dollars.End(xlDown))
    
    Range("A1:AZ1").Find("Dollars").Select
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    ActiveCell.EntireColumn.Offset(0, 1).Insert

Dim ptc As Range
Dim ften 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 ascalcr = dollarsr.Offset(0, 1)
    Set asvarr = dollarsr.Offset(0, 2)
    
    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

So as i stated in my reply to JLGWhiz, I realized the problem was that I defined the variable range and then inserted columns in front of (in this case on top of) where I assigned my variable ranges. This caused my variable to be undefined and therefore no longer contain my selected cell in the Autofill Destination range. I reorganized my sub to define my range variable after i insert the rows where I'll be writing my formulas, and my sub is now complete.

Thank you guys for your help, responding to your responses helped me work through it.

 

[JLGWhiz]

Thank you guys for your help, responding to your responses helped me work through it.

Happy to assist,
Regards, JLG