If Then else using an Array
 

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

If Then else using an Array
 

=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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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

If Then else using an Array
 

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

ooops Sorry

Mark

If Then else using an Array
 

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)



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

ooops Sorry

Mark

If Then else using an Array
 

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



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