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?

2
  • 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, 2015 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, 2015 at 1:49

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .