VBA code that looks for matching data from a different workbook

Question

So I have written this piece of code that manages to look for matching values for some data that is present in the lower part of the same worksheet. The data it searches from is present in the upper part of the same sheet. I have a separate column of keywords in both the upper and lower data through which the code loops through and then prints the matched row number or 'No match' if there was a match or not.

It works perfectly fine but I now want to modify it to work when the said lower part of the data is present in a completely different workbook.

Sub FindMatchingData() 
    For Row = 3124 To 6219
        searchVal = Cells(Row, 9).Value
        CheckVal = "xxx"
        Srow = 3  'Row number from where the upper part data starts

        While CheckVal <> searchVal And Srow < Row
            CheckVal = Cells(Srow, 9).Value
            Srow = Srow + 1
        Wend  

        If CheckVal = searchVal Then
            Cells(Row, 10).Value = Srow - 1
        Else
            Cells(Row, 10).Value = "No match"
        End If
    Next
End Sub

I'm unsure how to get a range of rows from that different workbook to enter into my For loop. I tried to create a variable mycell As Range and other variables are necessary and tried the below:

For Each mycell In wkb.Worksheets("Worksheetname").Range("A3:A3098")

But it doesn't seem to be working. I'm doing something wrong here. Maybe I should modify my While conditions?

Answer 1

Assuming, that you are searching in a different file and writing results in the file you have the macro in, I would do it like this:

Sub FindMatchingData() 

Dim this_ws as Worksheet
Set this_ws = ThisWorkbook.Worksheets("sheetname")

Dim other_ws as Worksheet
Set other_ws = Workbooks("other_filename").Worksheets("other_sheetname")

For Row = 3124 To 6219
    'searching in other_ws
    searchVal = other_ws.Cells(Row, 9).Value
    CheckVal = "xxx"
    Srow = 3  'Row number from where the upper part data starts

    While CheckVal <> searchVal And Srow < Row
        CheckVal = other_ws.Cells(Srow, 9).Value
        Srow = Srow + 1
    Loop

    'writing results to this ws
    If CheckVal = searchVal Then
        this_ws.Cells(Row, 10).Value = Srow - 1
    Else
        this_ws.Cells(Row, 10).Value = "No match"
    End If
Next

End Sub

Cells will by default look to the active sheet, that's why I defined two different sheets at the top.