CODEWITHSUNDEEP
vbaexcel
macworthy
macworthy

Reputation: 95

1004 Error: Cannot reset variable

I'm trying to do a very simple thing. I want to store a range of dynamic values in a variable. I use the following code: 

Sub Main()
Set RawStuff = Worksheets("Data Input").Range(Range("A1"), Range("A1").End(xlDown))
End Sub

The first time works fine, the second time I run it produces the following error:

Run-time error '1004': Application-defined or object-defined error

The only way to fix it is by restarting excel/exiting the workbook.

My guess is that the issue is that once a variable is set, it cannot be reset. For reference, I am creating this macro for a specific workbook.

Upvotes: 0

Views: 80

Answers (1)

Tim Williams
Tim Williams

Reputation: 166381

Set RawStuff = Worksheets("Data Input").Range(Range("A1"), Range("A1").End(xlDown))

This will fail if "Data Input" is not active, since by default Range("A1") will refer to the active sheet.

It's best to qualify all calls to Range() or Cells() with a worksheet object:

With Worksheets("Data Input")
    Set RawStuff = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With

The code above should work regardless of which worksheet is active (as long as the active workbook is the correct one)

Upvotes: 4

Related Questions