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 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

--

Gary Keramidas
Excel 2003


"ironhydroxide" wrote in message
...
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

ironhydroxide wrote:
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

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.

ironhydroxide wrote:

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

--

Dave Peterson

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

--
Rick (MVP - Excel)


"ironhydroxide" wrote in message
...
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

All very good suggestions. thanks to all who replied.


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"ironhydroxide" wrote in message
...
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