Search an Array

[ironhydroxide]

I Have an Array with 25 strings stored. I want to search this array for a
certain string and get the placement in the array where this string is
located. if MYArray() = ("Example", "Trial", "work".... Et Cetera) and i
want to find where "Trial" is in the Array, it would give me the integer of 2

Thanks in advance

Ironhydroxide

 

[Gary Keramidas]

you can try something like this:

For i = LBound(arr) To UBound(arr)
If arr(i) = "YourText" Then
'If UCase(arr(i)) = "YOURTEXT" Then 'use this if you don't want case
sensitive
Debug.Print i +1
Exit For
End If
Next
End Sub

 

[smartin]

I Have an Array with 25 strings stored. I want to search this array for a
certain string and get the placement in the array where this string is
located. if MYArray() = ("Example", "Trial", "work".... Et Cetera) and i
want to find where "Trial" is in the Array, it would give me the integer of 2

Thanks in advance

Ironhydroxide

You have to loop through the array until you find what you are looking
for. This will work for 1-based arrays:

Function WhereIsIt(SomeArray As Variant, _
ThingToFind As String) As Long
Dim i As Long
i = 1
Do While i < UBound(SomeArray) And _
SomeArray(i) <> ThingToFind
i = i + 1
Loop
If SomeArray(i) = ThingToFind Then WhereIsIt = i
End Function

 

[Dave Peterson]

Dim myArr as variant
dim res as variant
dim myStr as string

myArr = array("example","trial", "work")

mystr = "trial"

res = application.match(mystr, myarr,0)
if isnumeric(res) then
msgbox res
else
msgbox "no match"
end if

ps. Application.match is not case sensitive. Trial, TRIAL, TrIaL, ... will all
be treated the same.

 

[Rick Rothstein]

Is your array a one-dimensional Sting array? If so, here is another method
to consider...

Function ArrayIndex(Word As String, Arr() As String) As Long
Dim Temp As String
Temp = Chr(1) & LCase(Join(Arr, Chr(1))) & Chr(1)
If InStr(Temp, Chr(1) & LCase(Word) & Chr(1)) = 0 Then Exit Function
ArrayIndex = UBound(Split(Split(Temp, LCase(Word))(0), Chr(1)))
End Function

Just pass the word you want to find and the array into the function and it
will returns the index position of that word within the array (it returns 0
if the word is not located within the array). Here is a simple example
showing its usage...

Sub TestIndexFinder()
Dim MYArr() As String
MYArr = Split("Example,Trial,work,book,left,right", ",")
Debug.Print ArrayIndex("trial", MYArr)
End Sub

 

[ironhydroxide]

All very good suggestions. thanks to all who replied.