Excel VBA Autofill with Range(cells()) not working

Question

I'm trying to get this code to work but I can’t seem to get rid of the 1004 runtime error when running this line:

Range("F14:F53").AutoFill Destination:=Range(Cells(14, 6), Cells(53, colChosenMonth)).Select, Type:=xlFillValues.

The purpose of the code is to check which column to extend the formulas in Range("F14:F53") to, variable colChosenMonth does that and it is returning the correct column number (10). But I can’t seem to get that to work with the AutoFill formula. If I try to select the range it is working just fine.

I have posted the complete code below and I hope someone can help me spot the error, it is quite far down in the code.

 Sub PopulateBudget()
    ' Läs in värden från QV till PB
    '
    Dim colChosenMonth As Long
    '
    Sheets("Periodiserad budget").Select
    Selection.Worksheet.Unprotect

    Range("E14").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(QV!C8,QV!C9,RC1,QV!C7,""<=""&R10C)"

    Range("E14").Select
    Selection.AutoFill Destination:=Range("E14:E53"), Type:=xlFillValues
    Range("E14:E53").Select

    Range("E34").Select
    Selection.ClearContents

    Range("F14").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(QV!C8,QV!C9,RC1,QV!C7,""<=""&R10C)-SUM(RC5:RC[-1])"

    Range("F14").Select
    Selection.AutoFill Destination:=Range("F14:F53"), Type:=xlFillValues
    Range("F14:F53").Select

    Range("F34").Select
    Selection.ClearContents

    colChosenMonth = Application.Match(Range("vald_månad"), Worksheets("Periodiserad budget").Range("A10:BJ10"), 0)
    Range(Cells(14, 5), Cells(53, colChosenMonth)).Select

    'ROW BELOW DOESNT WORK --> Run-time error '1004': autofill method of range class failed.

    Range("F14:F53").AutoFill Destination:=Range(Cells(14, 6), Cells(53, colChosenMonth)).Select, Type:=xlFillValues

    'Selection.AutoFill Destination:=Range("F14:BJ53"), Type:=xlFillValues
    'Range("F14:BJ53").Select

    Range(Cells(14, 5), Cells(53, colChosenMonth)).Copy
    Range(Cells(14, 5), Cells(53, colChosenMonth)).Select
    'Range("E14:BJ53").Copy
    'Range("E14:BJ53").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Selection.Worksheet.Protect
End Sub

While in this particular case it was caused by a typo, that kind of code is prone to errors. You should see stackoverflow.com/q/17733541/11683 and stackoverflow.com/q/10714251/11683. — GSerg

Vityata Vityata · Accepted Answer · 2018-07-12T15:01:09

Remove the .Select in the line Range("F14:F53").AutoFill Destination:=Range(Cells(14, 6), Cells(53, colChosenMonth)).Select, Type:=xlFillValues.

Read How to avoid using Select in Excel VBA, there are some good ideas how to avoid .Select and .Activate. Probably it is a good idea to submit your working code to https://codereview.stackexchange.com, you may get some worthy ideas.

Excel VBA Autofill with Range(cells()) not working

1 Answers