VBA code for searching and flagging cell text in multiple columns

anothertallguy

New Member
Joined
Sep 15, 2014
Messages
3
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!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
You may have to adjust for your columns.
usage:
vWord = inputbox("Enter Search Word","Search")
FindInRow vWord

Code:
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

New Member
Joined
Sep 15, 2014
Messages
3
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

New Member
Joined
Sep 15, 2014
Messages
3
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top