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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,588
Members
452,860
Latest member
jroberts02

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
Back
Top