Cell Reference AutoFill Error 1004 Object Defined Error

Question

I'm trying to autofill across a set of columns that changes depending on a user input. However when I reference the cells of the range it gives me an error. The error comes up on the last line

Dim stocknum As Integer
stocknum = Sheets("Sheet1").Cells(1, 1)

Dim a As Integer
Dim j As Integer
Dim k As Integer
Dim LastRow1 As Long

Dim LastRow() As String
ReDim LastRow(0 To stocknum + 1) As String
Dim LastCol As Integer


j = stocknum + 2
k = stocknum + 5
l = stocknum + stocknum + 5

Worksheets("Sheet1").Activate
LastRow1 = (Range("B9").End(xlDown).Row) - 7
LastCol = (Range("B9").End(xlToRight).Column) - 1


Worksheets("stock_returns").Activate
Sheets("stock_returns").Cells(3, 2).Formula = "=('Sheet1'!B9-'Sheet1'!B10)/'Sheet1'!B10"
Sheets("stock_returns").Cells(3, 2).Select
Selection.AutoFill Destination:=Sheets("stock_returns").Range("Cells(3, 2), Cells(3, LastCol)")

The quick answer is remove the quotes around the cells references in the range. — Scott Craner, Dec 01 '15 at 03:43
The long answer is take time to learn how to avoid using .select. Look [here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for a good start. — Scott Craner, Dec 01 '15 at 03:47
also remove the `Sheets`-part at autofill... will cause an error every time — Dirk Reichel, Dec 01 '15 at 04:05
I removed the select line and the quotations and now it won't fill the columns. There is not error so I am unsure as to why it's not filling in. `Dim LastCol As Integer` `LastCol = (Range("B9").End(xlToRight).Column) - 2` `Worksheets("stock_returns").Activate Worksheets("stock_returns").Cells(3, 2).FormulaR1C1 = "=(Sheet1!R[6]C-Sheet1!R[7]C)/Sheet1!R[7]C" Cells(3, 2).AutoFill Destination:=Range(Cells(3, 2), Cells(3, LastCol)), Type:=xlFillDefault` — RPInerd92, Dec 01 '15 at 21:14

score 0 · Answer 1 · answered Dec 01 '15 at 04:34

Try the following: You make mistake in last line. Just remove double quote ("") from Cells() function part.

     Dim stocknum As Integer
        stocknum = Sheets("Sheet1").Cells(1, 1)

        Dim a As Integer
        Dim j As Integer
        Dim k As Integer
        Dim LastRow1 As Long

        Dim LastRow() As String
        ReDim LastRow(0 To stocknum + 1) As String
        Dim LastCol As Integer


        j = stocknum + 2
        k = stocknum + 5
        l = stocknum + stocknum + 5

        Worksheets("Sheet1").Activate
        LastRow1 = (Range("B9").End(xlDown).Row) - 7
        LastCol = (Range("B9").End(xlToRight).Column) - 1


        Worksheets("stock_returns").Activate
        Sheets("stock_returns").Cells(3, 2).Formula = "=('Sheet1'!B9-'Sheet1'!B10)/'Sheet1'!B10"
        Sheets("stock_returns").Cells(3, 2).Select
        Selection.AutoFill Destination:=Sheets("stock_returns").Range(Cells(3, 2), Cells(3, LastCol))

Cell Reference AutoFill Error 1004 Object Defined Error

1 Answers1