Type Mismatch Error in Array

[JacyErdelt]

Can someone tell me why I might be getting the "type mismatch" error when I
get to the second line? These numbers must be strings because they are a
"label" for a store (like a store name), and the number that is entered by
the user and is compared to these numbers later in this code is confirmed as
a string in the other piece of code. Ideally, when a store number is entered
by the user, VBA should determine if the store entered is out of the network
based on its label (which is one of these 3 stores).

Dim OutOfNetwork As String
OutOfNetwork = Array("182", "244", "1457")

I can get the correct result if I run thru a bunch of nested if or case
statments, but I was hoping to keep the code short by having it only compare
its value to the store in this array. Thank you for your insight!

 

[RB Smissaert]

OutOfNetwork needs to be declared as a variant, so:
Dim OutOfNetwork As Variant
or just
Dim OutOfNetwork

You then have an 0-based variant array and you can use that like this:
For i = 0 To UBound(OutOfNetwork)
Msgbox OutOfNetwork(i)
Next i
etc.

RBS

 

[Jacob Skaria]

Type cast as Variant

If this post helps click Yes

 

[Chip Pearson]

You then have an 0-based variant array and you can use that like this:
For i = 0 To UBound(OutOfNetwork)

Always best practice to use LBound instead of hard coding a lower
bound. If you were to copy the code to another module with a different
Option Base directive, the code might fail.

For i = LBound(OutOfNetwork) To UBound(OutOfNetwork)
'.....


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)