VBA code for searching and flagging cell text in multiple columns

[anothertallguy]

I have seven columns of names. Within each row, a name only appears one time. For each row, I would like to be run a search across all seven columns for a specific name, and flag that row if the name appears.

For example, with 4 columns:

John Smith | Jane Doe | Amy Jones | Jude Law
Jane Doe | Devin Shaw| Steve Levitt | Jimmy Stewart
Douglas John| Steve Levitt| Jane Doe| Jeremy Lane


I would like to flag each row (with a "1" in a new column) in which any of the columns contains the name "Jane Doe," regardless of which column she falls under.


I thought this would be easy until I couldn't find any answers out there. Hope this makes sense and I appreciate any insight!

 

[ranman256]

You may have to adjust for your columns.
usage:
vWord = inputbox("Enter Search Word","Search")
FindInRow vWord

Sub FindInRow(byval pvWord)
dim c as integer
CONST kMARK = 3    'WHAT OFFSET COLUMN TO MARK
range("A2").select
While ActiveCell..Value <> ""
    for c = 0 to 2
       
       if instr(ActiveCell.Offset(0, c).value ,pvWord) >0 then      'search next column
         ActiveCell.Offset(0, kMARK).value = 1
         goto  skipit       
       end if
    next
skipit:          
   ActiveCell.Offset(1, 0).Select    'next row
Wend
End Sub

 

[anothertallguy]

Thanks! Could you elaborate on the vWord and FindInRow you listed at the top? Where in the code do I enter the search word?

Also, could you identify which components of the code I will need to revise for my own spreadsheet?

 

[anothertallguy]

This code only works when I remove the vWord and FindInRow texts at the top. However, when I do that, I have no way to search for a specific name. Could you please explain how I can use these to search for a specific name? Thank you for your help!