CODEWITHSUNDEEP
vbaexcel
Sándor Szilágyi
Sándor Szilágyi

Reputation: 13

run time error 1004 application defined or object defined error - looping over same range in different worksheets

Could you please help me in solving the following issue:

Sub PBLSearch()

    Dim PBLRng As Range
    Dim PBL As Range
    Dim SearchRng As Range
    Dim SourceWsNr As Integer

    For SourceWsNr = 1 To 2
        Debug.Print Workbooks("Book1.xlsx").Sheets(SourceWsNr).Name
        Set PBLRng = Workbooks("Book1.xlsx").Sheets(SourceWsNr).Range(Range("A1"), Range("A1").End(xlDown))
        For Each PBL In PBLRng.Cells
            Debug.Print PBL.Value
        Next PBL
    Next SourceWsNr

End Sub

The code works fine when the SourceWsNr equals 1, but as soon as this changes to 2, i get the error mentioned in the subject. Is it because i re-set the PBLRng variable? I couldn't find any solution to this problem...

Thank you very much in advance.

Best Regards,

S. Sz.

Upvotes: 1

Views: 50

Answers (1)

arcadeprecinct
arcadeprecinct

Reputation: 3777

The problem is the line

Workbooks("Book1.xlsx").Sheets(SourceWsNr).Range(Range("A1"), Range("A1").End(xlDown))

The Range("A1") and Range("A1").End(xlDown) are referencing the cells on the active worksheet which is why it fails on the second sheet (the arguments for the range method of a sheet have to be on the same sheet). You have to specify the sheet each time when calling Range This should do it:

With ActiveWorkbook.Sheets(SourceWsNr)
    Set PBLRng = .Range("A1", .Range("A1").End(xlDown))
End With

Edit: you might also consider using another way to find the last row, like .Cells(.Rows.count,1).End(xlUp). It depends on your specific needs though.

Upvotes: 1

Related Questions