Searching an Array
 

Excel 2002, WinXP
I built an array of people's names, say People().
I want to find out if Harry is in that array.
I don't want to find Harry's position within the array, just if he is in
the array.
How would I code that search?

Thanks for your help. Otto

Searching an Array
 

Sub Test()

Dim arr(0 To 100, 0 To 200) As String
Dim i As Long
Dim c As Long

arr(50, 50) = "Harry"

For i = 0 To 100
For c = 0 To 200
If arr(i, c) = "Harry" Then
MsgBox "found Harry", , ""
Exit Sub
End If
Next
Next

End Sub


RBS

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I built an array of people's names, say People().
I want to find out if Harry is in that array.
I don't want to find Harry's position within the array, just if he is in
the array.
How would I code that search?

Thanks for your help. Otto

Searching an Array
 

RB
Thanks for the help. I was hoping I could do it with a simple Find but,
alas, no joy. Otto
"RB Smissaert" wrote in message
...
Sub Test()

Dim arr(0 To 100, 0 To 200) As String
Dim i As Long
Dim c As Long

arr(50, 50) = "Harry"

For i = 0 To 100
For c = 0 To 200
If arr(i, c) = "Harry" Then
MsgBox "found Harry", , ""
Exit Sub
End If
Next
Next

End Sub


RBS

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I built an array of people's names, say People().
I want to find out if Harry is in that array.
I don't want to find Harry's position within the array, just if he is
in
the array.
How would I code that search?

Thanks for your help. Otto

Searching an Array
 

Hi Otto,

One way:

Dim MyCrit as String

MyCrit = "Harry"

If Application.CountIf(People, MyCrit) Then
' Harry found
Else
'Harry not found
End If

---
Regards,
Norman



"Otto Moehrbach" wrote in message
...
RB
Thanks for the help. I was hoping I could do it with a simple Find
but,
alas, no joy. Otto
"RB Smissaert" wrote in message
...
Sub Test()

Dim arr(0 To 100, 0 To 200) As String
Dim i As Long
Dim c As Long

arr(50, 50) = "Harry"

For i = 0 To 100
For c = 0 To 200
If arr(i, c) = "Harry" Then
MsgBox "found Harry", , ""
Exit Sub
End If
Next
Next

End Sub


RBS

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I built an array of people's names, say People().
I want to find out if Harry is in that array.
I don't want to find Harry's position within the array, just if he is
in
the array.
How would I code that search?

Thanks for your help. Otto

Searching an Array
 

Hi Otto,

Please ignore my response - I should learn to read!

---
Regards,
Norman

Searching an Array
 

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

=MsgBox ArrayCountIf(People,"Harry") 0

will display True or False

Alan Beban
Otto Moehrbach wrote:

RB
Thanks for the help. I was hoping I could do it with a simple Find but,
alas, no joy. Otto
"RB Smissaert" wrote in message
...

Sub Test()

Dim arr(0 To 100, 0 To 200) As String
Dim i As Long
Dim c As Long

arr(50, 50) = "Harry"

For i = 0 To 100
For c = 0 To 200
If arr(i, c) = "Harry" Then
MsgBox "found Harry", , ""
Exit Sub
End If
Next
Next

End Sub


RBS

"Otto Moehrbach" wrote in message
...

Excel 2002, WinXP
I built an array of people's names, say People().
I want to find out if Harry is in that array.
I don't want to find Harry's position within the array, just if he is

in

the array.
How would I code that search?

Thanks for your help. Otto

Searching an Array
 

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

=MsgBox ArrayCountIf(People,"Harry") 0

will display True or False
....

What's the leading '=' for?

This only disguises the underlying looping. And it's not immediately obvious
it'd be anywhere near as fast as the inline code


Dim x As Variant, f As Boolean
For Each x in People
If x = "Harry" Then
f = True
Exit For
End If
Next x


However, if the OP needs to do this frequently in his code, then FAR BETTER
to use a Scripting Dictionary object to hold names as key property and index
number (if all names distinct) or count of names (if names not all distinct)
within array as item. This object's .Exists method would be very efficient
for large arrays.

Always use the best tools for the task. Hash tables are best for this sort
of thing.

Searching an Array
 

Something like:

Option Explicit

Sub testIt()
Dim x(1 To 10), i As Integer

For i = 1 To 10
x(i) = Chr(64 + i)
Next i
On Error Resume Next
i = Application.WorksheetFunction.Match("harry", x, 0)
MsgBox "harry " & IIf(Err.Number 0, "not ", "") & "found"

Err.Clear
x(UBound(x)) = "harry"
i = Application.WorksheetFunction.Match("harry", x, 0)
MsgBox "harry " & IIf(Err.Number 0, "not ", "") & "found"
On Error GoTo 0

End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , ottomocobia97
@bellsouth.net says...
Excel 2002, WinXP
I built an array of people's names, say People().
I want to find out if Harry is in that array.
I don't want to find Harry's position within the array, just if he is in
the array.
How would I code that search?

Thanks for your help. Otto