CODEWITHSUNDEEP
excelvba
David Vinnicombe
David Vinnicombe

Reputation: 39

Method 'Range' of object '_Worksheet' failed despite qualifying ranges

I'm running into an issue and I can't figure it out. Questions with the same error seem to have a different issue.

I have the following code:

Option Explicit
Sub Bank_match()
    Dim x As Range, y As Range
    Dim FirstSheet As Worksheet, SecondSheet As Worksheet
    Dim Txn_count_1 As Integer, Txn_count_2 As Integer
    Dim i As Integer

    Set FirstSheet = Worksheets("Sheet1")
    Set SecondSheet = Worksheets("Sheet2")

    With FirstSheet
        Set x = Cells.Find(What:="Description", After:=Cells(1, 1), LookIn:=xlValues _
            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            Txn_count_1 = Range(x.offset(1, 0), x.offset(1, 0).End(xlDown)).Count
    End With

    With SecondSheet
        Set y = Cells.Find(What:="Description", After:=Cells(1, 1), LookIn:=xlValues _
            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            Txn_count_2 = Range(y.offset(1, 0), y.offset(1, 0).End(xlDown)).Count
    End With

    For i = 1 To Txn_count_1
            If FirstSheet.Range(x).offset(i, 1).Value = SecondSheet.Range(y).offset(i, 2).Value Or _
            FirstSheet.Range(x).offset(i, 2).Value = SecondSheet.Range(y).offset(i, 1).Value Then
                FirstSheet.Range(x.offset(i, 1)).EntireRow.Interior.ColorIndex = 6
                SecondSheet.Range(y.offset(i, 1)).EntireRow.Interior.ColorIndex = 6
            End If
    Next i
End Sub

Effectively, I'm trying to iterate through bank statements and highlight the rows of transactions that appear on both sheets. My problem is that I get the error on the 'If statement' line.

At first I thought it might be my sheet references, but if I replace 'FirstSheet' and 'SecondSheet' with Worksheets("Sheet1") and Worksheets("Sheet2") respectively, I then get "Application-defined or object-defined error" on the same line. I get the same error if I also qualify the range with 'ThisWorkbook'.

Any thoughts?

Upvotes: 0

Views: 43

Answers (1)

BigBen
BigBen

Reputation: 49998

Drop the FirstSheet.Range and SecondSheet.Range... x and y are already range objects:

If x.Offset(i, 1).Value = y.offset(i, 2).Value...

Though you need to check if the Find was successful... otherwise x or y might be Nothing... using the following:

If Not x Is Nothing Then ' and similarly for y

And you are missing a period . before Cells and Range

Set x = .Cells
Set y = .Cells
Txn_count_1 = .Range(...
Txn_count_2 = .Range(...

Upvotes: 4

Related Questions