If Then else using an Array

[Sh0t2bts]

Hi All,

I have an array with 29 names held in it, I am wanting to do a if
statement using the content of the Array.

If "MyName" is in ArrayName() Then
Do somthing
else
Do somthing else
End If

I don't know how to go about setting it out.

Can anyone advise?

Many Thanks

Mark

 

[Bernard Liengme]

=IF(COUNTIF(A1:A100,"MyName")>0,"I'm here", "I'm absent")
Just replace A1:A100 by the array name --- but no () please
best wishes

 

[Sh0t2bts]

Forgot to mention I am doing this in a Macro, so would need the VBA
version.

ooops Sorry

Mark

 

[Bob Phillips]

If Not IsError(Application.Match(some_value, myArray,0)) Then
Do somthing
else
Do somthing else
End If


--
---
HTH

Bob


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

 

[Rick Rothstein \(MVP - VB\)]

You can probably use the Filter function to do your test...

If UBound(Filter(ArrayNames, "MyName")) >= 0 Then
' Do something
Else
' Do something else
End If

The above will do an exact case search (so that "rick" would not match
"Rick"). If you need a case insensitive search, you could use this...

If UBound(Filter(ArrayNames, "ue", , vbTextCompare)) >= 0 Then

for the If/Then test instead. I don't think you can get any false positives
doing it this way... I mention this because Filter works like InStr and will
find substrings with in the whole text; but that shouldn't matter for the
type of test you described as it will also find the exact match as well as
any substring matches.

Rick