Excel Custom Auto-filters in VBA? 1

[kthawkins]

I'm looking for information on the use of the 'Begins With' autofilter in VBA, the helpfiles do not list anything, and Googling for information hasn't worked (but it did lead me here).

Can anyone help?

 

[TonyJollans]

Hi kthawkins,

Exactly what information do you want. Begins with is fairly self explanatory.

If you have "aa", "ab", and "bb" in your column, begins with "a" will filter out the "bb", leaving "aa" and "ab" visible, etc.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kthawkins]

Bear with me, this could get long and complicated.....

I have two ODBC sheets in the workbook and I have created a 'lookup' from one to the other. By using one field in the first sheet, I assign the cell value to a parameter, I autofilter the second sheet using the pasrameter and copy the result into a third sheet where additional information is also used.

We have modified our systems to provide a shortened initial field, but this is taking time to work through the databases.

Eventually all the fields will be the shortened version but it is expected that this may take upto six months. If there wasn't this delay I could carry on using the existing VBA code, but the code only works on fields exactly matching the original one.

In the meantime I need to search the second sheet using the shortened field whilst the original length field still exists in some records.

If I do the filter manually I can use the 'Begins With' autofilter to find the information I require, but I can't see any way of doing this in VBA, if I create a macro to see the result, 'Begins With' gives a field like '12345*' and uses the * as a wild-card, but I can't use the parameter value with a *.

I may be barking up the wrong tree, but I would appreciate some assistance.

Many thanks

Kevin

 

[TonyJollans]

Hi Kevin,

I'm not sure I really understand all of what you say, but why can't you use the asterisk?

When you say parameter, do you mean a VBA variable or something else? If a variable, you can use [blue]Criteria = "=" & YourVariable & "*"[/blue]. If something else, come back.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kthawkins]

Hi Tony,

That's great, problem solved, even if I don't understand what's going on.

I can't find any information on using the & in the Help file, where can I find an explanation of the fix please?

Many thanks

Kevin

 

[SkipVought]

Are you wanting to do a parameter query using a wildcard (*)?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kthawkins]

Hi Skip,

I'm still learning VBA and I couldn't think of the right term which was 'variable', Tony's previous reply has now sorted my existing problem, but I don't understand how the code works.

Many thanks for your reply.

Kevin

 

[SkipVought]

Do you have code that does not work, or it works but you don't understand how?

Post your code and get some help.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kthawkins]

Skip,

This is the bit of code that Tony provided;

Criteria = "=" & YourVariable & "*"

and I'm now using it like this.....

Selection.AutoFilter Field:=2, Criteria1:="=" & partlookup & "*" and this works.

My original code was;

Selection.AutoFilter Field:=2, Criteria1:=partlookup.

I understand how Tony's code concatenates the '=partlookup*' but why do I need the second '='? I was looking for an explanation of this.

Kevin

 

[TonyJollans]

Hi Kevin,

If you look up concatenation in Help you should find information on the two concatenation operators (& and +).

As for the two equal signs, they refer to different things ...

Criteria1 is a parameter passed to the autofilter. The way to tell the autofilter about it is by saying that Criteria1 is equal to something, i.e.

[tt] Criteria1[/tt][red][tt]:=[/tt][/red][tt]something[/tt].

This something is the criterion for the autofilter; you want to select all cells with a value beginning with, in this case, your variable, partlookup. In other words all cells where:

[tt] cellvalue=partlookup*[/tt].

When specified as a criterion, the cellvalue is implied and all that needs specifying is:

[tt] =partlookup*[/tt].

The comparison string is composed of two literals ("=" and "*") and a variable which needs to be substituted by VBA for the actual value before passing the complete string to Excel. To make sure VBA interprets it like this you concatenate (using &) the separate elements with the literals in quotes and the variable without:

[tt] "=" & partlookup & "*"[/tt].

So, the first equals sign is saying that Criteria1 is equal to something and the second one is part of the criteria, saying that the cell value must be equal to something, hence:

[tt] Criteria1[/tt][red][tt]:=[/tt][/red][tt] "[/tt][blue][tt]=[/tt][/blue][tt]" & partlookup & "*"[/tt].

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kthawkins]

Hi Tony,

Thanks for this, it's been a great help.

Regards

Kevin