Creating a "Search All" function
 

Hi there -

I have a little problem here and I was wondering if anyone could help
me out with this. I've created a database and already set up advanced
search macros. It selects a certain column and user can enter a string
to perform the search.

But, I was also wondering if anyone know a way where I can also have a
search where I can search a key word in all columns.

Creating a "Search All" function
 

Hi Tammy,

Try turning on the macro recorder while you perform the operation manually.

This should provide you with code which can be adapted and incorporated into
your existing code.


---
Regards,
Norman



"Tammy" wrote in message
oups.com...
Hi there -

I have a little problem here and I was wondering if anyone could help
me out with this. I've created a database and already set up advanced
search macros. It selects a certain column and user can enter a string
to perform the search.

But, I was also wondering if anyone know a way where I can also have a
search where I can search a key word in all columns.

Creating a "Search All" function
 

I'm sorry I don't understand.....

I've recorded some macro's such as refreshing the advanced filter,
where it will "show all" and delete the user inputs in the fields. But
the type of search I'm trying to do is for example...I want to search
for the word "alteration" in columns a, b, c, and d and list the rows
that contain "alteration" in either one of those columns.

Creating a "Search All" function
 

Hi Tammy,

One way would be to use a helper column

Try putting the formula

=COUNTIF(A2:C2,"=alternative")0

in (say) D2 and copy down as far as you need. Then filter using the column D
criterion of TRUE.

If the process is to be automated, turn on the macro recorder while you
perform these steps.

If you need any assistance in editing the recorder code, post back with
details of the problems you experience.


--
Regards,
Norman


"Tammy" wrote in message
oups.com...
I'm sorry I don't understand.....

I've recorded some macro's such as refreshing the advanced filter,
where it will "show all" and delete the user inputs in the fields. But
the type of search I'm trying to do is for example...I want to search
for the word "alteration" in columns a, b, c, and d and list the rows
that contain "alteration" in either one of those columns.

Creating a "Search All" function
 

Just to Add to Norman's excellent advice,

Also look at the help example for the FindNext method in Excel VBA help

Whatever you anchor the find command with is the area that is searched

Cells.Find . . . searches all cells

Columns(A:D).Find . . . searches columns A to D

--
Regards,
Tom Ogilvy


"Norman Jones" wrote in message
...
Hi Tammy,

One way would be to use a helper column

Try putting the formula

=COUNTIF(A2:C2,"=alternative")0

in (say) D2 and copy down as far as you need. Then filter using the column
D
criterion of TRUE.

If the process is to be automated, turn on the macro recorder while you
perform these steps.

If you need any assistance in editing the recorder code, post back with
details of the problems you experience.


--
Regards,
Norman


"Tammy" wrote in message
oups.com...
I'm sorry I don't understand.....

I've recorded some macro's such as refreshing the advanced filter,
where it will "show all" and delete the user inputs in the fields. But
the type of search I'm trying to do is for example...I want to search
for the word "alteration" in columns a, b, c, and d and list the rows
that contain "alteration" in either one of those columns.