Testing for a value's inclusion in an array
 

If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

.... then how do I test for intEFX being equal to a value in the array after
assigning it a value, and without going If intEFX = A(0) OR intEFX = A(1),
etc? That's a reasonable way to go with a tiny array, but there's got to be a
more elegant solution for larger arrays, right?

Tried looking for a help topic that combined IF and arrays, but not having
any luck, so any help provided would be much appreciated. Thank you.

Testing for a value's inclusion in an array
 

iPos = 0
On Error Resume Next
iPos = Application.Match(intEFX,A,0)
On Error goto 0
If iPos 0 Then
'found it

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"danhattan" wrote in message
...
If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

... then how do I test for intEFX being equal to a value in the array
after
assigning it a value, and without going If intEFX = A(0) OR intEFX = A(1),
etc? That's a reasonable way to go with a tiny array, but there's got to
be a
more elegant solution for larger arrays, right?

Tried looking for a help topic that combined IF and arrays, but not having
any luck, so any help provided would be much appreciated. Thank you.

Testing for a value's inclusion in an array
 

danhattan wrote:
If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

... then how do I test for intEFX being equal to a value in the array after
assigning it a value, and without going If intEFX = A(0) OR intEFX = A(1),
etc? . . .

Application.IsNumber(Application.Match(intEFX, A, 0))

Alan Beban

Testing for a value's inclusion in an array
 

Or, slightly shorter

IsNumeric(Application.Match(intEFX, A, 0))

In article ,
Alan Beban <unavailable wrote:

Application.IsNumber(Application.Match(intEFX, A, 0))

Testing for a value's inclusion in an array
 

Thanks Bob.

May I ask another question? (Hoping you just said yes, cuz here I go.)

When I select Application.Match and press F1, there's no help topic to
display. Further, when I type in Application, press".", Match isn't on the
list choices that automatically come up. As a result, I can only guess what
this is doing and hope you can confirm.

It's taking three parameters (intEFX, the array, position 0 in the array),
starting at position 0 then moving through the array til it either does or
does not find the value. At least that's what I thought, except that when I
set the third parameter at 2 and set the value of intEFX to match the value
at position 0, intEFX was still identified as being in the array. So what is
that third parameter?

Thanks again for the help. It definitely works.





"Bob Phillips" wrote:

iPos = 0
On Error Resume Next
iPos = Application.Match(intEFX,A,0)
On Error goto 0
If iPos 0 Then
'found it

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"danhattan" wrote in message
...
If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

... then how do I test for intEFX being equal to a value in the array
after
assigning it a value, and without going If intEFX = A(0) OR intEFX = A(1),
etc? That's a reasonable way to go with a tiny array, but there's got to
be a
more elegant solution for larger arrays, right?

Tried looking for a help topic that combined IF and arrays, but not having
any luck, so any help provided would be much appreciated. Thank you.

Testing for a value's inclusion in an array
 

Bob is using the worksheet function Match. Go into Excel itself and look for
the Match Worksheet function in help.

for Bob's code to work, I believe you would have to cast Ipos as Long or
Integer or use Application.WorksheetFunction.Match

another way

Dim ipos as Variant
iPos = Application.Match(intEFX,A,0)
If Not iserror(iPos) Then
'found it



--
Regards,
Tom Ogilvy


"danhattan" wrote:

Thanks Bob.

May I ask another question? (Hoping you just said yes, cuz here I go.)

When I select Application.Match and press F1, there's no help topic to
display. Further, when I type in Application, press".", Match isn't on the
list choices that automatically come up. As a result, I can only guess what
this is doing and hope you can confirm.

It's taking three parameters (intEFX, the array, position 0 in the array),
starting at position 0 then moving through the array til it either does or
does not find the value. At least that's what I thought, except that when I
set the third parameter at 2 and set the value of intEFX to match the value
at position 0, intEFX was still identified as being in the array. So what is
that third parameter?

Thanks again for the help. It definitely works.





"Bob Phillips" wrote:

iPos = 0
On Error Resume Next
iPos = Application.Match(intEFX,A,0)
On Error goto 0
If iPos 0 Then
'found it

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"danhattan" wrote in message
...
If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

... then how do I test for intEFX being equal to a value in the array
after
assigning it a value, and without going If intEFX = A(0) OR intEFX = A(1),
etc? That's a reasonable way to go with a tiny array, but there's got to
be a
more elegant solution for larger arrays, right?

Tried looking for a help topic that combined IF and arrays, but not having
any luck, so any help provided would be much appreciated. Thank you.

Testing for a value's inclusion in an array
 

LOL!

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JE McGimpsey" wrote in message
...
Or, slightly shorter

IsNumeric(Application.Match(intEFX, A, 0))

In article ,
Alan Beban <unavailable wrote:

Application.IsNumber(Application.Match(intEFX, A, 0))

Testing for a value's inclusion in an array
 

It might work with a 3rd parameter of 2, but trying getting an unordered set
of values in the array and looking for one. It will work with 0 as the
value, but not with 2.

As Tom said, you do need to declare iPos as type Long.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"danhattan" wrote in message
...
Thanks Bob.

May I ask another question? (Hoping you just said yes, cuz here I go.)

When I select Application.Match and press F1, there's no help topic to
display. Further, when I type in Application, press".", Match isn't on the
list choices that automatically come up. As a result, I can only guess
what
this is doing and hope you can confirm.

It's taking three parameters (intEFX, the array, position 0 in the array),
starting at position 0 then moving through the array til it either does or
does not find the value. At least that's what I thought, except that when
I
set the third parameter at 2 and set the value of intEFX to match the
value
at position 0, intEFX was still identified as being in the array. So what
is
that third parameter?

Thanks again for the help. It definitely works.





"Bob Phillips" wrote:

iPos = 0
On Error Resume Next
iPos = Application.Match(intEFX,A,0)
On Error goto 0
If iPos 0 Then
'found it

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"danhattan" wrote in message
...
If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

... then how do I test for intEFX being equal to a value in the array
after
assigning it a value, and without going If intEFX = A(0) OR intEFX =
A(1),
etc? That's a reasonable way to go with a tiny array, but there's got
to
be a
more elegant solution for larger arrays, right?

Tried looking for a help topic that combined IF and arrays, but not
having
any luck, so any help provided would be much appreciated. Thank you.

Testing for a value's inclusion in an array
 

Thanks. As luck as would have it I did declare iPos as an integer, so that
probably explains why his code did work. And thanks for the tip on the nature
of the function. I now get the third parameter as it's a lot like the VLOOKUP
function. Very cool, and thanks again.

"Tom Ogilvy" wrote:

Bob is using the worksheet function Match. Go into Excel itself and look for
the Match Worksheet function in help.

for Bob's code to work, I believe you would have to cast Ipos as Long or
Integer or use Application.WorksheetFunction.Match

another way

Dim ipos as Variant
iPos = Application.Match(intEFX,A,0)
If Not iserror(iPos) Then
'found it



--
Regards,
Tom Ogilvy


"danhattan" wrote:

Thanks Bob.

May I ask another question? (Hoping you just said yes, cuz here I go.)

When I select Application.Match and press F1, there's no help topic to
display. Further, when I type in Application, press".", Match isn't on the
list choices that automatically come up. As a result, I can only guess what
this is doing and hope you can confirm.

It's taking three parameters (intEFX, the array, position 0 in the array),
starting at position 0 then moving through the array til it either does or
does not find the value. At least that's what I thought, except that when I
set the third parameter at 2 and set the value of intEFX to match the value
at position 0, intEFX was still identified as being in the array. So what is
that third parameter?

Thanks again for the help. It definitely works.





"Bob Phillips" wrote:

iPos = 0
On Error Resume Next
iPos = Application.Match(intEFX,A,0)
On Error goto 0
If iPos 0 Then
'found it

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"danhattan" wrote in message
...
If I have the following in my code ...

Dim intEFX As Integer
Dim A As Variant
A = Array(1, 4, 6, 9)

... then how do I test for intEFX being equal to a value in the array
after
assigning it a value, and without going If intEFX = A(0) OR intEFX = A(1),
etc? That's a reasonable way to go with a tiny array, but there's got to
be a
more elegant solution for larger arrays, right?

Tried looking for a help topic that combined IF and arrays, but not having
any luck, so any help provided would be much appreciated. Thank you.