Referencing a range in another worksheet.

[JWyath]

I want to loop over the values in a range on my ActiveWorksheet (Sheet1) and compare them to values in a range on another worksheet (Sheet2).

Here's my original code:

Sub LoopOverRange()
    Dim fromColumn As range
    Dim toColumn As range
    
    Set fromColumn = Sheet1.range("C2", range("C20000").End(xlUp))
    Set toColumn = Sheet2.range("A2", range("A20000").End(xlUp))

    For Each c In fromColumn.Cells
        If c <> "null" Then
            For Each d In toColumn.Cells
                If c.Value = d.Value Then
                    Call doSomething(c, d)
                End If
            Next
        End If
    Next
End Sub

This gives me "runtime error 1004"

Here's two test bits of code :

Sub testRange()
    Dim myRange As range
    Set myRange = Sheet1.range("C2", range("C20000").End(xlUp))
End Sub

This works.

Sub testRange()
    Dim myRange As range
    Set myRange = Sheet2.range("C2", range("C20000").End(xlUp))
End Sub

This gives me "runtime error 1004"

How can I achieve my goal and why does my first test code work while my second test code fails?

 

[Andrew Poulsom]

Welcome to MrExcel.

You need to qualify both instances of the Range property:

Sub testRange()
    Dim myRange As range
    Set myRange = Sheet1.Range("C2", Sheet1.Range("C20000").End(xlUp))
End Sub

 

[JWyath]

Thanks for the prompt reply, Andrew.

That worked, although I still think it's peculiar that the first of my test cases works while the second doesn't, even if I activate Sheet2 beforehand.

 

[Andrew Poulsom]

Excel uses the ActiveSheet if the property is unqualified, except when the code is in the module for a Worksheet when that sheet is used.