CODEWITHSUNDEEP
vbaexcel
Akshat Agrawal
Akshat Agrawal

Reputation: 47

Error 1004 : autofill method of range class failed vba excel 2010

I am running the below VBA code - 

lastColumn = Cells(5, Columns.count).End(xlToLeft).Column
count_dates = Range("H2").Value

Set cellSource = Range(Cells(6, 13).Address)
Set cellTarget = Range(Cells(6, 13), Cells(count_dates + 5, lastColumn))
cellSource.AutoFill Destination:=cellTarget, Type:=xlFillDefault

The code is running perfectly fine on one PC (Excel 2016), but not running on the other (Excel 2010). I am unable to figure out the reason for the same.

The cell (6,13) has a formula which i want to drag both horizontally right and vertically down

The error I get is - Error 1004 : Autofill method of range class failed

Upvotes: 0

Views: 1768

Answers (1)

Variatus
Variatus

Reputation: 14383

In Excel 2010 Autofill works in a single direction only, either horizontal or vertical. Therefore, if you wish to fill a 2-dimensional range you should first fill one row horizontally (including the source cell) and then use that row as source copy the entire row down. The code below does that.

Sub AutoFillRange()

    Dim lastColumn As Long
    Dim count_Dates As Long
    Dim cellSource As Range, cellTarget As Range

    lastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
    count_Dates = Range("H2").Value

    Set cellSource = ActiveSheet.Cells(6, "M")
    If lastColumn <> cellSource.Column Then
        ' return an unexpected result if lastColumn is smaller than cellSource.Column
        Set cellTarget = cellSource.Resize(1, Abs(lastColumn - cellSource.Column + 1))
        cellSource.AutoFill Destination:=cellTarget, Type:=xlFillDefault
        Set cellSource = cellTarget
    End If
    Set cellTarget = cellSource.Resize(count_Dates, cellSource.Columns.Count)
    cellSource.AutoFill Destination:=cellTarget, Type:=xlFillDefault
End Sub

Upvotes: 1

Related Questions