Object variable not set

Question

I have a simple routine to copy data from a user-input sheet (Sheet1) to a protected sheet (Sheet2) for automatic workings. I'm having trouble debugging a simple line of code, just hoping someone can help me clear this one up :)

Sub CopyData()

Dim LastRowSh1 As Long
Dim LastColSh1 As Long
Dim Sheet1Data() As Variant
Dim LastRowSh2 As Long
Dim LastColSh2 As Long

    With Sheets("Sheet1")

        ' Defines data range in Sheet1.
        LastRowSh1 = .Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
        LastColSh1 = .Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
        ' Loads the contents of Sheet1 data into array Sheet1Data.
        Sheet1Data = .Range(.Cells(7, 1), .Cells(LastRowSh1, LastColSh1)).Value

    End With

    With Sheets("Sheet2")

        ' Removes any existing filters in Sheet2.
        If .AutoFilterMode = True Then .AutoFilter.ShowAllData

        ' Defines preexisting data range in Sheet2 (if any).
        LastRowSh2 = .Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

I get Run-time error '91' on this line above. Can't for the life of me figure out why as it's the same With statement as above which is working just fine!

        LastColSh2 = .Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
        ' Clears preexisting data.
        .Range(.Cells(7, 1), .Cells(LastRowSh2, LastColSh2)).ClearContents

        ' Repopulates with the contents of array Sheet1Data.
        .Range(.Cells(7, 2), .Cells(LastRowSh1, LastColSh1 + 1)).Value = Sheet1Data

    End With

End Sub

user4039065 · Accepted Answer · 2014-12-01 10:55:59Z

You are shortcutting your range references a bit too much. The [A1] you are using for the After:= parameter of the Range.Find method is insufficient to show parentage to the With Sheets("Sheet2") ... End With. Sheet1 is the ActiveSheet so that is the parent of [A1] and you cannot search After:=Sheet1!A1 on Sheet2. If Sheet1 was not the active sheet, you would have the same problems in the .Find within With Sheets("Sheet1") ... End With.

You can prove this by using the following in the VBE's Immediate Window.

?[A1].address(0,0,,True)

Changing the active sheet in the workbook and re-running the command will change the worksheet name returned.

Just change the cell reference to something more appropriate like .Cells(1,1).

LastRowSh2 = .Cells.Find("*", .Cells(1,1), , , xlByRows, xlPrevious).Row

I'd recommend that you do this for both of the Range.Find code lines.

Collectives™ on Stack Overflow

Object variable not set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related