Let's say I have an array with 100 string elements. Now I have to
check if a particular string is same as one of the elements in the
array, is there a simple way of doing so instead of using many
If/Elseif statements?

If not, I suppose one would have to use some kind of lookup functions.
In that case, does it necessarily mean one would have to include a
data worksheet (invisible or otherwise) in the file for the lookup?

For example, there are 100 string elements in column H. I then create
the newArray by applying various left/mid/right functions to column H.
How do I check if strVariable is same as one of the elements in
newArray? Do I have to list all the elements of newArray on a data
worksheet first?

TIA

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

ArrayCountIf(newArray,strVariable)

Alan Beban

Shatin wrote:
Let's say I have an array with 100 string elements. Now I have to
check if a particular string is same as one of the elements in the
array, is there a simple way of doing so instead of using many
If/Elseif statements?

If not, I suppose one would have to use some kind of lookup functions.
In that case, does it necessarily mean one would have to include a
data worksheet (invisible or otherwise) in the file for the lookup?

For example, there are 100 string elements in column H. I then create
the newArray by applying various left/mid/right functions to column H.
How do I check if strVariable is same as one of the elements in
newArray? Do I have to list all the elements of newArray on a data
worksheet first?

TIA

Shatin,

Sub test()
Dim arr(100 - 1) As String, i As Long, strVariable As String, blnFound
As Boolean

For i = 0 To 100 - 1
arr(i) = "Hello " & i & i & i
Next

strVariable = "Hello 565656"

blnFound = False
For i = 0 To 100 - 1
If arr(i) = strVariable Then
blnFound = True
Exit For
End If
Next

If blnFound Then
MsgBox "Search string found in array element " & i
Else
MsgBox "Search string not found"
End If
End Sub


Rob

"Shatin" wrote in message
om...
Let's say I have an array with 100 string elements. Now I have to
check if a particular string is same as one of the elements in the
array, is there a simple way of doing so instead of using many
If/Elseif statements?

If not, I suppose one would have to use some kind of lookup functions.
In that case, does it necessarily mean one would have to include a
data worksheet (invisible or otherwise) in the file for the lookup?

For example, there are 100 string elements in column H. I then create
the newArray by applying various left/mid/right functions to column H.
How do I check if strVariable is same as one of the elements in
newArray? Do I have to list all the elements of newArray on a data
worksheet first?

TIA

it depends on whether your array elements are ordered or not. If they are, a lot of time can be gained using intelligent look-up algorithms, especially when your array has a lot of elements. Otherwise you'll just have to compare each array element with your reference string, where you can exit the loop when the array elements are unique or when you are interested in the first instance only.

So when your array is invariant, and you have to look up elements in it many times, sorting your array might be a good idea.

Shatin,

Here is one method that will work in VBA, but not from a worksheet

Function InArray(thisValue, thisArray) As Boolean
Dim c As Range

Range("A" & Rows.Count).Resize(1, UBound(thisArray)).Value = thisArray
Set c = Range("A" & Rows.Count).Resize(1,
UBound(thisArray)).Find(thisValue)
InArray = Not c Is Nothing

End Function


Example call

If InArray(4, myArray) Then
...

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Shatin" wrote in message
om...
Let's say I have an array with 100 string elements. Now I have to
check if a particular string is same as one of the elements in the
array, is there a simple way of doing so instead of using many
If/Elseif statements?

If not, I suppose one would have to use some kind of lookup functions.
In that case, does it necessarily mean one would have to include a
data worksheet (invisible or otherwise) in the file for the lookup?

For example, there are 100 string elements in column H. I then create
the newArray by applying various left/mid/right functions to column H.
How do I check if strVariable is same as one of the elements in
newArray? Do I have to list all the elements of newArray on a data
worksheet first?

TIA

Alan and Rob, many thanks for your replies.

A newbie question: How come everyone here almost always declares i as
long and seldom as integer even when i is a small number?

Because 32 bit OS will work in Long integers, so in any 32 bit system it
will convert 16 bit variables into 32 bit, and the convert back when
returning the result. Thus it is more efficient to use long explicitly

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Shatin" wrote in message
om...
Alan and Rob, many thanks for your replies.

A newbie question: How come everyone here almost always declares i as
long and seldom as integer even when i is a small number?

Thanks for this useful bit of info, Bob. And I thought I was doing
things by the book!

"Bob Phillips" wrote in message ...
Because 32 bit OS will work in Long integers, so in any 32 bit system it
will convert 16 bit variables into 32 bit, and the convert back when
returning the result. Thus it is more efficient to use long explicitly