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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,171
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,962
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top