Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 22nd, 2006, 05:56 PM
Registered User
Join Date: Apr 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Search string in Excel

Hey everybody,

I am trying to write routine that will search a large Excel 2003 worksheet, say cells "a2:p5000" for a set of three criteria, all text. If the three criteria are present then I am trying to get the spreadsheet to highlight the row in some way ( I don't care how just as long as it can be found easily).
Can this be done in Excel 2003? I have had varying success trying to get this to work but can't get it to work all the way around. I am not a programmer, just trying to make my job allot easier. I would appreciate any help anybody can offer.


Reply With Quote
  #2 (permalink)  
Old June 1st, 2006, 11:11 PM
Registered User
Join Date: Jun 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts


Not being a programmer, the way I would do it would be this...

Add 2 columns to your data and join the three criteria in one of the new columns (say it's column A). You can do this through the CONCATENATE function. Alternatively, you can simply use '&' between cell references. i.e. =C4&D4&E4

Outside of your data enter the criteria you are searching for in the same format as it is showing in column 1. The type the word "yes" in the cell to the left of the cell where you typed your criteria. So if your criteria is in cell A1, then type "yes" into B1.

The next step is use a vlookup formula. In the second new column you added use the VLOOKUP function to see if each rows criteria matches your defined criteria at A1. So assume you are typing the formula into Column B row 4 =IF(VLOOKUP(A4,A1:B1,2,false).

If a row as the same criteria as you specified in A1 then the result should be a "yes" in the relevant cell in column B. Copy the formula down as far as your data goes.

The last step is to use conditional formatting. Conditionally format each row to say if the cell in column B equals B1 then format the cell with whatever pattern you want. Conditional formatting is available from the Format menu.

If you have a large amount of data to look through, highlighting the row may not be your best option. You may just want to skip the conditional formatting and once you have copied the VLOOKUP formula down column B just use a data filter to single out all the rows with "yes".

Hope that all makes sense...


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
New text search doesn't preselect search string planoie Visual Studio 2005 0 July 23rd, 2007 06:47 AM
Search string Mantis PHP How-To 5 June 2nd, 2005 03:30 AM
Search certain charactor/ symbol in my string life_s Ng General .NET 3 January 4th, 2005 08:48 AM
MSAccess search string question gilgalbiblewheel Access 2 August 3rd, 2004 06:01 PM
Search for Character in String Kaynor09 Excel VBA 2 April 24th, 2004 11:13 AM

All times are GMT -4. The time now is 12:18 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.