Searching an Array

[Otto Moehrbach]

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

 

[RB Smissaert]

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]

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

 

[Norman Jones]

Hi Otto,

One way:

Dim MyCrit as String

MyCrit = "Harry"

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

 

[Norman Jones]

Hi Otto,

Please ignore my response - I should learn to read!

 

[Alan Beban]

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

 

[Harlan Grove]

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.

 

[Tushar Mehta]

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