Problems with filtering!!
 

Hi.

I need some held developing a macro that will do the following:

Filter data row by row for certain text but there's a catch - it mus
be able to look at one column and, if it does not find the text, loo
at another column before eliminating it.

Is this possible? I have written some very long-winded VBA code usin
Autofilter but I am getting lost in my own work. It seems like
relatively straight-forward function so I am assuming that there is a
easier way to do it.

Any help would be much appreciated.

Thanks,

Andre

--
Barry Jiv
-----------------------------------------------------------------------
Barry Jive's Profile: http://www.excelforum.com/member.php...fo&userid=2428
View this thread: http://www.excelforum.com/showthread.php?threadid=37893

Problems with filtering!!
 

I would create a helper column with a formula to do the looking, and filter
on that.

--
HTH

Bob Phillips

"Barry Jive" wrote
in message ...

Hi.

I need some held developing a macro that will do the following:

Filter data row by row for certain text but there's a catch - it must
be able to look at one column and, if it does not find the text, look
at another column before eliminating it.

Is this possible? I have written some very long-winded VBA code using
Autofilter but I am getting lost in my own work. It seems like a
relatively straight-forward function so I am assuming that there is an
easier way to do it.

Any help would be much appreciated.

Thanks,

Andrew


--
Barry Jive
------------------------------------------------------------------------
Barry Jive's Profile:
http://www.excelforum.com/member.php...o&userid=24281
View this thread: http://www.excelforum.com/showthread...hreadid=378935

Problems with filtering!!
 

or.... maybe use advanced filter?

--
Cheers
Nigel



"Barry Jive" wrote
in message ...

Hi.

I need some held developing a macro that will do the following:

Filter data row by row for certain text but there's a catch - it must
be able to look at one column and, if it does not find the text, look
at another column before eliminating it.

Is this possible? I have written some very long-winded VBA code using
Autofilter but I am getting lost in my own work. It seems like a
relatively straight-forward function so I am assuming that there is an
easier way to do it.

Any help would be much appreciated.

Thanks,

Andrew


--
Barry Jive
------------------------------------------------------------------------
Barry Jive's Profile:
http://www.excelforum.com/member.php...o&userid=24281
View this thread: http://www.excelforum.com/showthread...hreadid=378935

Problems with filtering!!
 

Thanks for your help guys.

I had initially toyed with helper columns but I started getting lost in
my own sheet.

For anyone else's information, I ended up doing the following:

First, I inserted a helper column as suggested. The formula I used was

=IF(I3=CheckLists!F$1,1,IF(V3=CheckLists!H$1,1,0))
This looked at the value in one column (I3) first and compares it with
the criteria selected by the user for the filter. If it returns a TRUE,
the formula then looks at the second column and compares it. If it
returns a TRUE, the cell is attribtued a '1'; if not, the cell is
attributed a '0'.

I then created a userform with various Combo Box lists, containing
criteria to select for the filter. I set the ControlSource of each
Combo Box to a cell in a separate worksheet. In this example, this
worksheet is labelled 'CheckLists'. By referring back to this cell in
the formula, the criteria selected in the filter can be checked against
the data in the specified columns (I3 and V3).

Finally, I created a 'Search' button on the userform which activates
the Autofilter on the column containing the formula above. It filters
for any cells containing a '1', thus leaving the rows that contain the
criteria specified by the user.

Sorry the explanation's a bit of a ramble, but hopefully it will help
someone else.

Andrew


--
Barry Jive
------------------------------------------------------------------------
Barry Jive's Profile: http://www.excelforum.com/member.php...o&userid=24281
View this thread: http://www.excelforum.com/showthread...hreadid=378935