Creating a "Search All" function

[Tammy]

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.

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[Tammy]

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.

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>