Using wildcard in VBA comparison? need a hint please.

[KR]

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

 

[Tom Ogilvy]

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

 

[KR]

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]

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.

 

[KR]

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]

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.