I have three comboboxes on a worksheet that I pull values from. Each one has
a value of "All", followed by a list of items:

List 1:
All
Dog
Cat
Horse
etc.

I set a variable equal to each of the three combobox values (V1 ="Dog", V2 =
"Blue", V3 = "Pizza")

Now I need to loop through lines in a worksheet and check to see when
columns A, C, and D are equal to the individual info from the three
comboboxes. This is easy when I have specific values to match ("Dog") but
when I try to code for the "All" category I end up with a whole boatload of
embedded loops that end up confusing me and not working properly.

Essentially my original code looked something like:

If MyRange1 = V1 then
If MyRange2 = V2 then
If MyRange3 = V3 then
'do stuff
End if
End if
End if

If any selection is "All" I want to just skip that 'If' and keep going to
the next embedded if, rather than skipping over the whole section of code. I
looked at wildcards, but "Dog" isn't the same as "*", so that didn't work as
I'd hoped.

What would be the best approach to accept any MyRange value when my
comparison is "All"?

Many thanks in advance,
Keith


--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

This basically duplicates the functionality of the the built in Autofilter.
Are you not aware of autofilter or instead do you have some reason not to
use it.

for each cell in Range(cells(2,1),Cells(rows.count,1).End(xlup))
cnt = 0
if (V1 = "All" or Cell.Value = V1) and _
(V2 = "All" or Cell.Offset(0,2).Value = V2) and _
(V3 = "All" or Cell.Offset(0,3).Value = V3) then
cell.EntireRow.Hidden = False
else
cell.EntireRow.Hidden = True
end if
Next

--
Regards,
Tom Ogilvy


"KR" wrote in message
...
I have three comboboxes on a worksheet that I pull values from. Each one
has
a value of "All", followed by a list of items:

List 1:
All
Dog
Cat
Horse
etc.

I set a variable equal to each of the three combobox values (V1 ="Dog", V2
=
"Blue", V3 = "Pizza")

Now I need to loop through lines in a worksheet and check to see when
columns A, C, and D are equal to the individual info from the three
comboboxes. This is easy when I have specific values to match ("Dog") but
when I try to code for the "All" category I end up with a whole boatload
of
embedded loops that end up confusing me and not working properly.

Essentially my original code looked something like:

If MyRange1 = V1 then
If MyRange2 = V2 then
If MyRange3 = V3 then
'do stuff
End if
End if
End if

If any selection is "All" I want to just skip that 'If' and keep going to
the next embedded if, rather than skipping over the whole section of code.
I
looked at wildcards, but "Dog" isn't the same as "*", so that didn't work
as
I'd hoped.

What would be the best approach to accept any MyRange value when my
comparison is "All"?

Many thanks in advance,
Keith


--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Some additional background info; I have a userform with 4 pages, the data
from each page goes into 4 different worksheets. I'm taking the raw data
from all four sheets and pulling it into an array, performing a bunch of
calculations on arrays in memory, then pasting the data into a pre-formatted
data worksheet. A number of named ranges represent sections of that final
data worksheet, and are used to create bunches of graphs.

My current attempt is to only bring certain data into my array (prior to
calculations) so that the user can perform some more detailed analysis (by
looking at the graphs)- for example, only pulling data into the array based
on specific categories of data (dogs only, blue only, or pizza only) rather
than everything in the category (animals, colors, foods). I can screen for
the specific entries based on my sample code below, but I don't know how to
ignore a criteria if the user selected "All".

Maybe instead of wildcards I should do something with multiple statements?
e.g.:
If (MyRange1 = V1) or (V1 = "All") then 'etc.
I suppose that would still pick up just the specific items when they are
selected, and all of them when "All" is selected?

I'll test that and see if it works- but just for the sake of learning, if
there is a way to do this with wildcards (e.g. replace "All" with "*" and
use that conditionally in a statement) I'd still love to learn how.
:)
Thanks!
Keith


"Tom Ogilvy" wrote in message
...
This basically duplicates the functionality of the the built in
Autofilter.
Are you not aware of autofilter or instead do you have some reason not to
use it.

for each cell in Range(cells(2,1),Cells(rows.count,1).End(xlup))
cnt = 0
if (V1 = "All" or Cell.Value = V1) and _
(V2 = "All" or Cell.Offset(0,2).Value = V2) and _
(V3 = "All" or Cell.Offset(0,3).Value = V3) then
cell.EntireRow.Hidden = False
else
cell.EntireRow.Hidden = True
end if
Next

--
Regards,
Tom Ogilvy


"KR" wrote in message
...
I have three comboboxes on a worksheet that I pull values from. Each one
has
a value of "All", followed by a list of items:

List 1:
All
Dog
Cat
Horse
etc.

I set a variable equal to each of the three combobox values (V1 ="Dog",
V2
=
"Blue", V3 = "Pizza")

Now I need to loop through lines in a worksheet and check to see when
columns A, C, and D are equal to the individual info from the three
comboboxes. This is easy when I have specific values to match ("Dog")
but
when I try to code for the "All" category I end up with a whole boatload
of
embedded loops that end up confusing me and not working properly.

Essentially my original code looked something like:

If MyRange1 = V1 then
If MyRange2 = V2 then
If MyRange3 = V3 then
'do stuff
End if
End if
End if

If any selection is "All" I want to just skip that 'If' and keep going
to
the next embedded if, rather than skipping over the whole section of
code.
I
looked at wildcards, but "Dog" isn't the same as "*", so that didn't
work
as
I'd hoped.

What would be the best approach to accept any MyRange value when my
comparison is "All"?

Many thanks in advance,
Keith


--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.

All isn't a wildcard, so you would still have to recognize that the
selection is All. If you are going to determine that, then it would be
redundant to try to use a wildcard.

Maybe instead of wildcards I should do something with multiple statements?
e.g.:
If (MyRange1 = V1) or (V1 = "All") then 'etc.

Wow, wish I had thought of that.

--
Regards,
Tom Ogilvy



"KR" wrote in message
...
Some additional background info; I have a userform with 4 pages, the data
from each page goes into 4 different worksheets. I'm taking the raw data
from all four sheets and pulling it into an array, performing a bunch of
calculations on arrays in memory, then pasting the data into a
pre-formatted
data worksheet. A number of named ranges represent sections of that final
data worksheet, and are used to create bunches of graphs.

My current attempt is to only bring certain data into my array (prior to
calculations) so that the user can perform some more detailed analysis (by
looking at the graphs)- for example, only pulling data into the array
based
on specific categories of data (dogs only, blue only, or pizza only)
rather
than everything in the category (animals, colors, foods). I can screen for
the specific entries based on my sample code below, but I don't know how
to
ignore a criteria if the user selected "All".

Maybe instead of wildcards I should do something with multiple statements?
e.g.:
If (MyRange1 = V1) or (V1 = "All") then 'etc.
I suppose that would still pick up just the specific items when they are
selected, and all of them when "All" is selected?

I'll test that and see if it works- but just for the sake of learning, if
there is a way to do this with wildcards (e.g. replace "All" with "*" and
use that conditionally in a statement) I'd still love to learn how.
:)
Thanks!
Keith


"Tom Ogilvy" wrote in message
...
This basically duplicates the functionality of the the built in
Autofilter.
Are you not aware of autofilter or instead do you have some reason not
to
use it.

for each cell in Range(cells(2,1),Cells(rows.count,1).End(xlup))
cnt = 0
if (V1 = "All" or Cell.Value = V1) and _
(V2 = "All" or Cell.Offset(0,2).Value = V2) and _
(V3 = "All" or Cell.Offset(0,3).Value = V3) then
cell.EntireRow.Hidden = False
else
cell.EntireRow.Hidden = True
end if
Next

--
Regards,
Tom Ogilvy


"KR" wrote in message
...
I have three comboboxes on a worksheet that I pull values from. Each
one
has
a value of "All", followed by a list of items:

List 1:
All
Dog
Cat
Horse
etc.

I set a variable equal to each of the three combobox values (V1
="Dog",
V2
=
"Blue", V3 = "Pizza")

Now I need to loop through lines in a worksheet and check to see when
columns A, C, and D are equal to the individual info from the three
comboboxes. This is easy when I have specific values to match ("Dog")
but
when I try to code for the "All" category I end up with a whole
boatload
of
embedded loops that end up confusing me and not working properly.

Essentially my original code looked something like:

If MyRange1 = V1 then
If MyRange2 = V2 then
If MyRange3 = V3 then
'do stuff
End if
End if
End if

If any selection is "All" I want to just skip that 'If' and keep going
to
the next embedded if, rather than skipping over the whole section of
code.
I
looked at wildcards, but "Dog" isn't the same as "*", so that didn't
work
as
I'd hoped.

What would be the best approach to accept any MyRange value when my
comparison is "All"?

Many thanks in advance,
Keith


--
The enclosed questions or comments are entirely mine and don't
represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.

The proposed solution of multiple statements did work; the one that I'm
still curious about (wildcards) I never got working- I tried replacing the
string of "All" with the string of "*" (if V1 = "All" then V1 = "*") but
when I used statements like:

If MyRange1 = V1 then 'etc.

where MyRange1 = "dog" and now my V1 = "*", it was comparing the actual
strings - which aren't equal - instead of somehow incorporating the "*" as a
wildcard. I suspect there is some syntax specific to the use of wildcards
that I didn't find in my search of the help file. I'll keep looking...

:)

Thanks,
Keith



"Tom Ogilvy" wrote in message
...
All isn't a wildcard, so you would still have to recognize that the
selection is All. If you are going to determine that, then it would be
redundant to try to use a wildcard.

You can't use a wildcard in an equality comparison. You can look at the
Like operator


if Myrange.Value like "*" then

? "dog" like "*"
True
? "a$@Afsldkf" like "*"
True

not sure how that is a savings over

v1 = "All"

but you seem to be fixated on it.

--
Regards,
Tom Ogilvy


"KR" wrote in message
...
The proposed solution of multiple statements did work; the one that I'm
still curious about (wildcards) I never got working- I tried replacing the
string of "All" with the string of "*" (if V1 = "All" then V1 = "*") but
when I used statements like:

If MyRange1 = V1 then 'etc.

where MyRange1 = "dog" and now my V1 = "*", it was comparing the actual
strings - which aren't equal - instead of somehow incorporating the "*" as
a
wildcard. I suspect there is some syntax specific to the use of wildcards
that I didn't find in my search of the help file. I'll keep looking...

:)

Thanks,
Keith



"Tom Ogilvy" wrote in message
...
All isn't a wildcard, so you would still have to recognize that the
selection is All. If you are going to determine that, then it would be
redundant to try to use a wildcard.