Cycle through rows to match two cells on separate worksheets

Question

I'm looking to cycle through each row in a column on worksheet 1, to match two cells (a cell in column C from worksheet 1 with a cell in column E from worksheet 2).

If there is a match, I want to perform an action to an adjacent cell (I will work this bit out later) and then resume the loop until there are no more matches.

So far I have the following:

Sub Test3()
    Dim x As String
    Dim found As Boolean
    ' Select first line of data.
    Worksheets("PLANNER_ONGOING_DISPLAY_SHEET").Activate
    Range("C4").Select
    ' Set search variable value.
    x = "test 73"
    ' Set Boolean variable "found" to false.
    found = False
    ' Set Do loop to stop at empty cell.
    Do Until IsEmpty(ActiveCell)
        ' Check active cell for search value.
        If ActiveCell.Value = x Then
            found = True
            Exit Do
        End If
        ' Step down 1 row from present location.
        ActiveCell.Offset(1, 0).Select
    Loop
    ' Check for found.
    If found = True Then
        MsgBox "Value found in cell " & ActiveCell.Address
    Else
        MsgBox "Value not found"
    End If
End Sub

riskypenguin · Accepted Answer · 2019-10-02T13:30:17.777

0

You could take an easier approach, while also avoiding .Activate and .Select:

Sub Test3()
  Dim x As String, y As String
  Dim found As Boolean
  Dim i As Integer, lastRow As Long

  lastRow = Worksheets("PLANNER_ONGOING_DISPLAY_SHEET").Cells(Rows.Count, 3).End(xlUp).Row

  x = "test 73"

  For i = 4 To lastRow
    'If you need to compare it to a cell in sheet2 you can also set the value of x here
    y = Worksheets("PLANNER_ONGOING_DISPLAY_SHEET").Cells(i, 3).Value2
    If y = x Then
        ' Do action here, example:
        MsgBox "Value found in cell " & "C" & i
    End If

  Next i

End Sub

This will go through all the used cells in column C and compare them to x. If a cell matches, its row number is returned via a MsgBox.

edited Oct 02 '19 at 13:30

answered Oct 02 '19 at 13:28

riskypenguin

2,139
1
10
22

1

lastRow = Worksheets("PLANNER_ONGOING_DISPLAY_SHEET").cells(rows.count,3).End(xlUp).Row – Harassed Dad Oct 02 '19 at 13:30
@HarassedDad You're right, I just noticed it as well. It is corrected now. – riskypenguin Oct 02 '19 at 13:30
Thank you so much! Follow up - if i wanted to match against another cell as opposed to the text "test 73" is there an easy way to do that? For example, cell E2 on "Sheet 2"? – adamjamesb Oct 02 '19 at 14:58
Replace this `x = "test 73"` with `x = Sheets("Sheet2").Cells(2, 5).Value`. If this solved your problem please consider upvoting and accepting the answer so others might find it more easily. – riskypenguin Oct 02 '19 at 14:59

Cycle through rows to match two cells on separate worksheets

1 Answers1