0

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?

  • Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including the code they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?. – DavidPostill Aug 24 '15 at 12:33
  • The simplest way to do this is probably with a helper column. Combine first and last name in a new column and then search there. – fixer1234 Aug 25 '15 at 1:49
1

This code should work:

Public Sub searchfullname()
    fullname = InputBox("Input first and last name separated by _")
    namesarray = Split(fullname, "_")
    i = 2
    dataintable = True
    result = "No"
    m = ActiveSheet.Cells(i, 1)
    If m = "" Then dataintable = False
    While dataintable = True
        result = "No"
        If m = namesarray(0) Then
            n = ActiveSheet.Cells(i, 2)
            If n = namesarray(1) Then
                result = "Yes"
            End If
        End If
        ActiveSheet.Cells(i, 3) = result
        i = i + 1
        m = ActiveSheet.Cells(i, 1)
        If m = "" Then dataintable = False
    Wend
End Sub
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.