PDA

View Full Version : Solved: Search a spreadsheet



austenr
06-26-2007, 01:22 PM
Hi everyone,

This has probably been answered somewhere but I cant seem to find exactly what I want.

I need a way to search an entire worksheet for a variable or part of a variable entered by the user. For example, lets say that the user enteres Ja. There would be entries in the sheet for say Jackson, Sonja, Jameson, etc. So the search would have to find the characters anywhere within the string.

mdmackillop
06-26-2007, 01:26 PM
You need to search for xlPart. Are you looking to return all found files? What are you looking to do with the result?

austenr
06-26-2007, 01:31 PM
Hi Malcomb,

What I want to do is return all rows that match the partial string. The reason I need to do this is because our IT dept will not let me run my PHP app which works great on our server. So if you find "Ja" in 20 rows I would want to return all rows that meet the criteria in a VB form of some kind, something suitable for the net.

There would only be 4 columns to show, A,B,C,D. Thanks

lucas
06-27-2007, 06:46 AM
Are you searching a particular column?

austenr
06-27-2007, 07:00 AM
No the characters could appear anywhere on the sheet. Also, I can't find a resource but I would like the results to show on something like a browser window. That may be too much to ask though.

lucas
06-27-2007, 07:14 AM
Austenr,
I don't know about the browser part but here is a filtering solution which copies the rows containing the string....uses inputbox and I used ja to test to sheet 2....Maybe it's a start for you.

austenr
06-27-2007, 09:41 AM
Hey Steve. Thanks. That might work with the exception of 2 things. First hide the search data (very hidden). It should not be accessible to the user. Second, present the results in a VB form with column headings and the data underneath.

lucas
06-27-2007, 09:48 AM
one at a time....maybe we can sort it...what do you mean by this:

First hide the search data (very hidden). It should not be accessible to the user.
Are you talking about the main sheet with the data and the button to run the code?

lucas
06-27-2007, 10:16 AM
see how we're doing.....

austenr
06-27-2007, 12:14 PM
Doing fine. Now can the results be put into a VB form? With the buttons on the form that do the search and clear?

lucas
06-27-2007, 03:54 PM
How do you wish to show it on the form? Listbox...?

lucas
06-27-2007, 04:27 PM
see attached

mdmackillop
07-17-2007, 12:38 PM
Hi Austen,
Is this solved?

austenr
07-17-2007, 01:16 PM
Hi Malcomb. Yes it is. Sorry.