Type Mismatch Error in Array
 

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!

Type Mismatch Error in Array
 

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


"JacyErdelt" wrote in message
...
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!

Type Mismatch Error in Array
 

Type cast as Variant

If this post helps click Yes
---------------
Jacob Skaria


"JacyErdelt" wrote:

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!

Type Mismatch Error in Array
 

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)




On Sat, 28 Mar 2009 07:58:36 -0000, "RB Smissaert"
wrote:

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


"JacyErdelt" wrote in message
...
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!