macro error autofill method of range class failed error

Question

In my macro, I want it to fill rows A2 through A80 with the numbers 1 through 80. I put "1" in the first column and dragged down. It originally put 1 in all the columns, but then I clicked Fill Series and it listed the numbers the way I wanted it showing 1 through 80. Somehow when I tried to use the macro that I recorded, I got a run time error saying "autofill method of range class failed." I'm not sure what I am missing as I did not write the code but recorded it.

Range("A1").Select

Selection.AutoFill Destination:=Range("A2:A80"), Type:=xlFillSeries 

Range("A2:A80").Select

Answer 1

The macro recorder is far from perfect.

Here is an alternative:

Sub Macro1()
    Application.ScreenUpdating = False
    Range("A1").Formula2 = "=SEQUENCE(80)"
        ' Optional: replace formula with result
    With Range("A1:A80")
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

Answer 2

Hi Kayla

Here are two other codes you may try.

CODE 1

The code will create a 1 to 80 sequence from any selected cell (the active cell) downwards.

If you want to change the sequence, feel free to change/replace the value for the variable (Sq = 80) in the code.

Sub Sequence()

Dim Sq As Variant

Sq = 80

With ActiveCell

        .Value = 1

        .Offset(1).Resize(Sq - 1, 1).FormulaR1C1 = "=R[-1]C+1"

        .Offset(1).Resize(Sq - 1, 1).Value = .Offset(1).Resize(Sq - 1, 1).Value

End With

End Sub

CODE 2

This code is similar, but it will first, ask the user to enter the number the Sequence should end.

Sub AnySequence()

Dim Sq As Variant

Sq = InputBox(Prompt:="Enter a number", Title:="SEQUENCE", Default:=2)

''' ***Error Handlers ****

If Sq = False Then Exit Sub

If Not IsNumeric(Sq) Then Exit Sub

If Sq < 2 Then Exit Sub

If (CLng(Sq) - Sq) <> 0 Then Exit Sub

With ActiveCell

        .Value = 1

        .Offset(1).Resize(Sq - 1, 1).FormulaR1C1 = "=R[-1]C+1"

        .Offset(1).Resize(Sq - 1, 1).Value = .Offset(1).Resize(Sq - 1, 1).Value

End With

End Sub

RESULT

SUGGESTION

If you choose to save the code(s) in your PERSONAL.XLSB VBA project/workbook/file.

Then, you'll be able to run the macros on any Excel file, at your convenience.

I hope this helps you find a solution to your problem.

Regards

Jeovany

Answer 3

Thank you for your help!

Answer 4

Thank you for your help!