I am aware that you can use Match to find the position of an entry in an array, and the following code works as expected, returning an index of 4:
Sub test()
Dim my_array As Variant
Dim index As Variant
my_array = Array("pop goes the weasel", "bobs your uncle", "nod your head", "this is a test", "tool bag")
On Error Resume Next
index = Application.WorksheetFunction.Match("this is a test", my_array, 0)
On Error GoTo 0
If index = 0 Then
MsgBox "Not found"
Else
MsgBox "Index: " & index
End If
End Sub
The problem is if I don't know the EXACT text that will appear in the array, but only part of it. I am wanting to do something like the VBA .find lookat:=xlpart operation, but on an array rather than a worksheet.
The following code will return with the "Not Found" message.
Sub test()
Dim my_array As Variant
Dim index As Variant
my_array = Array("pop goes the weasel", "bobs your uncle", "nod your head", "this is a test", "tool bag")
On Error Resume Next
index = Application.WorksheetFunction.Match("test", my_array, 0)
On Error GoTo 0
If index = 0 Then
MsgBox "Not found"
Else
MsgBox "Index: " & index
End If
End Sub
Other than looping through the entire array and doing a Like test on each entry, is there a more elegant (ie. faster) way of searching an array when you only have part of the string?
Thanks
Bookmarks