Testing for a value's inclusion in an array

[danhattan]

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.

 

[Bob Phillips]

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)

 

[Alan Beban]

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

 

[JE McGimpsey]

Or, slightly shorter

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

 

[danhattan]

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.

 

[Tom Ogilvy]

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

 

[Bob Phillips]

LOL!

--
---
HTH

Bob

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

 

[Bob Phillips]

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]

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.