Search two columns in Microsoft excel 2013 and set the value of a third column with a specific value

Question

I want to search a specific text which is the firstname_lastname e.g John_Smith in worksheetA. WorksheetA has a column firstname and another column lastname. So after knowing which row has the firstname and lastname I searched for, I want to set the value of cell in the same row with a specific value.

For example if this is my worksheet:

Firstname     Lastname     Found
Matt          Damon        No
Smith         Andrew       No
John          Smith        No
Tom           Mark         No

and my search word is John_Smith the result will be

Firstname     Lastname     Found
Matt          Damon        No
Smith         Andrew       No
John          Smith        Yes
Tom           Mark         No

Any advice please how this can be done in VBA excel?

Answer 1

Here is one example, split the string into two separate strings. Then loop through the range.

The set up

The Code

Sub Button1_Click()
    Dim e As String, a As String, b As String
    Dim Rws As Long, rng As Range, c As Range

    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range(Cells(2, 1), Cells(Rws, 1))

    e = Range("E1")
    a = Left(e, InStr(e, "_") - 1)
    b = Right(e, InStr(e, "_"))

    For Each c In rng.Cells
        If c = a And c.Offset(0, 1) = b Then c.Offset(0, 2) = "Yes"
    Next c

End Sub

The Result