Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching an Array
Hi Otto,
Please ignore my response - I should learn to read! --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for data in an array. | Excel Discussion (Misc queries) | |||
Searching/Counting an array | Excel Discussion (Misc queries) | |||
Searching for a value in an Array | Excel Worksheet Functions | |||
searching in an array | Excel Discussion (Misc queries) | |||
Searching for a criteria in array of cells within an IF statement | Excel Worksheet Functions |