Selection.AutoFill Destination:=Range(strRange) causes Runtimeerror `1004` The AutoFill-method of the range object coudnt be run

Question

    Dim row As Integer
    Dim strRange As String

Selecting the Sheet

    Sheets("Matrix").Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _       "=SUMIF(Aufstellung!R13C2:R[997]C,Matrix!RC[-1],Aufstellung!R13C3:R[997]C)"
    Range("C3").Select
    
    row = Count() + 3 - 1
    strRange = "C3:C" & row  
    Range(strRange).Select
    Selection.AutoFill Destination:=Range(strRange), Type:=xlFillDefault

---> here comes the Runtimeerror 1004: The AutoFill-method of the range object coudnt be run

    strRange = "B3:D" & row
    
    Range(strRange).Select

    Selection.Copy
    
    strRange = "F3:H" & row
    
    Range(strRange).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Kassenblatt").Select
    
    row = Count() + 6 - 1
    strRange = "C6:F" & row
    
    Range(strRange).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,R[-1]C+1,"""")"
    Range("B6").Select
    
    strRange = "B6:B" & row

---> here will probably come the same Runtimeerror 1004: The AutoFill-method of the range object coudnt be run

    Selection.AutoFill Destination:=Range(strRange), Type:=xlFillDefault
    Range(strRange).Select

    Range("A6").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[5]>0,R1C4,"""")"
    Range("A6").Select
    
     strRange = "A6:A" & row

---> here will probably come the same Runtimeerror 1004: The AutoFill-method of the range object coudnt be run

    Selection.AutoFill Destination:=Range(strRange), Type:=xlFillDefault

End Sub

Helper Function for counting all rows, that have a value - can be ignored for the question

Public Function Count() As Integer
    
    Dim n As Integer
    
    n = Worksheets("Formeln & Daten").Range("G21:G1000").Cells.SpecialCells(xlCellTypeConstants).Count
    
    Count = n

End Function

Try replacing of `Selection.AutoFill Destintion:=Range(strRange), Type:=xlFillDefault` with `Range("C3").AutoFill Destintion:=Range(strRange), Type:=xlFillDefault`. No need to select **anything**... `ActiveCell.FormulaR1C1 = "=SUMIF(Aufstellung!R13C2:R[997]C,Matrix!RC[-1],Aufstellung!R13C3:R[997]C)"` should be `Range("C3").FormulaR1C1 = _ "=SUMIF(Aufstellung!R13C2:R[997]C,Matrix!RC[-1],Aufstellung!R13C3:R[997]C)" and so on... — FaneDuru, Jun 23 '21 at 14:00
for me, that error comes up when the range I'm trying to autofill doesn't line up with what I expect. First things first, you should work to condense all of the .Select statements as it will clean up your code and make it A LOT easier to ensure everything is lined up as you expect. Start here: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — sous2817, Jun 23 '21 at 14:02
I had a spelling error in Destintion and corrected it. Afterwards a new error was thrown. I edited the post. — Tobi S, Jun 23 '21 at 14:03

score 1 · Accepted Answer · answered Jun 23 '21 at 14:27

1

Try this:

Sheets("Matrix").Range(strRange) = "=" & "SUMIF(Aufstellung!R13C2:R[997]C,Matrix!RC[-1],Aufstellung!R13C3:R[997]C)"

answered Jun 23 '21 at 14:27

TorbenIT

259
2
12

Thanks, it works now! You're fantastic :) – Tobi S Jun 23 '21 at 14:28

Selection.AutoFill Destination:=Range(strRange) causes Runtimeerror `1004` The AutoFill-method of the range object coudnt be run

1 Answers1