Select Case String

[gmk]

Hi

I am trying to send a string to a Select Case as in...

Please Login or Register  to view this content.

This will work but what I want to do is compose 'sFruit' elsewhere in the code

Please Login or Register  to view this content.

I've tried a number of variations like

sFruit = """Apples"", ""Bananas""", ""Pears""" but it doesn't work.

How can I do this?

T.I.A.

[JieJenn]

Haven't test it out yet, but maybe make your list into an array

Please Login or Register  to view this content.

[StevenM]

If you are trying to determine if a fruit is in a string of fruits, try:

Please Login or Register  to view this content.

[gmk]

Hi

Please Login or Register  to view this content.

Works almost, however another issue has arisen by coincidence. I am searching a closed wb's fieldnames using ADODB and just want to pull required fieldnames. What happened in testing is I came across sFruit = "Red plums. " i.e. with a dot.
When the db is searched the fieldname in the db is interpreted as "Red plums# ". It is the # which is the issue now.
Therefore when substituting real strings the code becomes...
Instr("Apples, Red plums. , Pears", "Red plums# ") and that cannot match.
I have tried wildcards without effect.

For what it is worth the code looks like this...

The search criteria is built from one opened wb by selecting anything up to 11 fieldnames using a form. Once done a number of selected but unopened wbs are searched for similar fields.
It is better to do the search within the closed wb rather than import all the fields to the xla then search because of the varied construction of the closed wbs.

Please Login or Register  to view this content.

[gmk]

Hi JieJenn

How would you iterate through the array?

But apart from that another issue has arisen - see above to StephenM for an explanation please.

[StevenM]

Therefore when substituting real strings the code becomes...
Instr("Apples, Red plums. , Pears", "Red plums# ") and that cannot match.
I have tried wildcards without effect.

One way of handling this would be:

Please Login or Register  to view this content.

[StevenM]

For your information ... I've written two types of "IsFruit" functions.

Please Login or Register  to view this content.

[gmk]

By using "." rather than "" and then returning the fieldname in the same manner worked for me. Thank you.
What I have to do now is research whether it is just dots that are changed into hashes in a db. If not I could still be looking for a solution using Replace.

Please Login or Register  to view this content.