Searching in list of strings in VBA

[yeheya]

Hello Everyone,

I have list of string say "Apple","Mango","One","India","USA" ... list goes on..

I have to check if the user input is present in this list.

I am looking something like:

IF strUserInput in ("Apple","Mango","One","India","USA") THEN
MSGBOX "Found"
ENDIF

What is the most efficient way to do this in VBA?

Regards,
Yeheya

 

[Oorang]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ExampleCode()
<SPAN style="color:#00007F">Dim</SPAN> FruitRange <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> FruitArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Fruit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FruitLoop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> SpecialFruit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FruitCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#007F00">'Load Fruits into array</SPAN>
<SPAN style="color:#00007F">Set</SPAN> FruitRange = Range("A1:A6")
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Fruit <SPAN style="color:#00007F">In</SPAN> FruitRange
    <SPAN style="color:#00007F">If</SPAN> Trim(Fruit.Value) <> "" <SPAN style="color:#00007F">Then</SPAN>
        FruitLoop = FruitLoop + 1
        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> FruitArray(FruitLoop)
        FruitArray(FruitLoop) = Fruit.Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> Fruit
<SPAN style="color:#007F00">'Check Array for presence of Fruit</SPAN>
SpecialFruit = "Mango"
<SPAN style="color:#00007F">If</SPAN> FruitLoop <> 0 <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">For</SPAN> FruitLoop = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(FruitArray)
<SPAN style="color:#00007F">If</SPAN> FruitArray(FruitLoop) = SpecialFruit <SPAN style="color:#00007F">Then</SPAN> FruitCount = FruitCount + 1
<SPAN style="color:#00007F">Next</SPAN> FruitLoop
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
MsgBox "Found " & FruitCount & " " & SpecialFruit & "s!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[yeheya]

I feel there should be some better way of doing this, which will be more efficient than this.

Experts any comments...

Regards,
Yeheya

 

[yeheya]

Please comment!!!

I feel there should be some better way of doing this, which will be more efficient than this.

Experts any comments...

Regards,
Yeheya

 

[Oorang]

http://www.mrexcel.com/board2/viewtopic.php?p=873177&highlight=#873177