Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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.
Thanks.

Randy

  #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
Default

Echo10,

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...

Cheers,

Bruno.


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





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