autofill method of range class failed

jkicker

Board Regular
Joined
Jun 7, 2018
Messages
79
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Code:
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.
 
Upvote 0
Code:
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.
 
Upvote 0
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.
 
Upvote 0
I d also check the formula, as it has contradictions....

Code:
=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"))))
 
Upvote 0
I d also check the formula, as it has contradictions....

Code:
=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.
 
Upvote 0
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.
Code:
    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.
 
Upvote 0
Code:
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.
 
Upvote 0
Thank you guys for your help, responding to your responses helped me work through it.

Happy to assist,
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top