Search array and return position?

[Ed from AZ]

Is there a built-in way to search through each value position of an
array (string) for a match to another string, and when a mtach is
found return the array position of the match? So if I'm looking ot
match "here" and Array(5) is "here" then I get a return of "5"?

Ed

 

[Helmut Weber]

Hi Ed,

Is there a *built-in* way to search through each value position of an
array (string) for a match to another string,

not to my present knowledge, however:

Sub Macro4()
Dim sTmpArr() As String
Dim x As Long
sTmpArr = Split("1 2 3 4 5 6 7 8 9 0")
For x = LBound(sTmpArr) To UBound(sTmpArr)
If sTmpArr(x) = "1" Then
MsgBox x + 1
Exit Sub
End If
Next
End Sub

Beware of split() returning a 0 based array.
--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Ed from AZ]

So basically all I can do is iterate through each position and return
the count at which a match is found? I was afraid of that, and hoping
for an easier faster way.

Thanks, Helmut.

Ed

 

[Helmut Weber]

Hi Ed,

for a faster way

there is no way, when searching,
as to compare each item found to the item in question.
Of course, even VBA will stop comparing
as soon as a difference is found, like first letter.

One might code it in VBA,
which is probably slow in theory,
in C#, in assembler, or even in hardware.

The logic is the eternal same.

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[macropod]

Hi Ed,

An alternative approach with a string might be:

Sub Test()
Dim tStr As String
Dim oStr As String
Dim Pos As Integer
tStr = "the quick brown fox jumps over the lazy dog"
oStr = InputBox("String to find")
Pos = InStr(tStr, oStr)
Do While Pos <> 0
MsgBox Pos
Pos = InStr(Pos + 1, tStr, oStr)
Loop
End Sub

If you're only interested in the first occurrence you can replace the loop with
MsgBox Pos
which will return a 0 if no match is found

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

So basically all I can do is iterate through each position and return
the count at which a match is found? I was afraid of that, and hoping
for an easier faster way.

Thanks, Helmut.

Ed

 

[Klaus Linke]

Is there a built-in way to search through each value position of an
array (string) for a match to another string, and when a mtach is
found return the array position of the match? So if I'm looking ot
match "here" and Array(5) is "here" then I get a return of "5"?

You might look whether a Scripting.Dictionary is more appropriate for your
needs than an array.
Dim myDic As New Scripting.Dictionary
myDic.Add Key:="here", Item:="5"
Debug.Print myDic(Key:="here")

Regards,
Klaus

 

[Ed from AZ]

Thanks to all the suggestions.

My original idea was to build an array out of the contents of
individual table cells: ary(1) would be the contents of row 1, and so
forth. If I could search the array ahd find a match and return the
position, I would know what row the text is in. That way, all I have
to do is search the array, not iterate the table cells every time.

But I think I'm stuck with iteration.

Thanks anyway for chiming in.
Ed

 

[Klaus Linke]

Hi Ed,

My original idea was to build an array out of the contents
of individual table cells: ary(1) would be the contents of row 1,
and so forth. If I could search the array ahd find a match and
return the position, I would know what row the text is in.
That way, all I have to do is search the array, not iterate the
table cells every time.

Good plan! I often do it the same way.

But I think I'm stuck with iteration.

Sure, but once you have the stuff in an array, iteration is a lot faster
than iterating throught the table cells.

I'll post the code I use below.

Regards,
Klaus


Function Table2Variant(myTable As Table) As Variant
' reads uniform table into variant array.
' indices: row, column; 1-based.
' The table cells can contain paragraph marks and tabs.
' Usage: Dim vTable as Variant
' vTable=Table2Variant(myTable)
' MsgBox vTable(iRow, iCol)
Dim vTable() As Variant
Dim sText As String
sText = myTable.Range.Text
Dim i_Row As Long, i_Col As Long
Dim i_Row_Max As Long, i_Col_Max As Long
Dim i_pos_Old As Long, i_pos As Long
i_Row_Max = myTable.Rows.Count
i_Col_Max = myTable.Columns.Count
ReDim vTable(i_Row_Max, i_Col_Max)
i_pos_Old = 1
For i_Row = 1 To i_Row_Max
For i_Col = 1 To i_Col_Max
i_pos = InStr(i_pos_Old, sText, Chr(13) & Chr(7)) + 2
vTable(i_Row, i_Col) = MID(sText, i_pos_Old, i_pos - i_pos_Old - 2)
i_pos_Old = i_pos
Next i_Col
i_pos_Old = i_pos_Old + 2
Next i_Row
Table2Variant = vTable
End Function

Sub Table2Variant_TestIt()
Dim vTest() As Variant
Dim i As Long, j As Long
vTest = Table2Variant(ActiveDocument.Tables(1))
For i = 1 To UBound(vTest, 1)
For j = 1 To UBound(vTest, 2)
Debug.Print "row " & i, "col " & j, vTest(i, j)
Next j
Next i
End Sub

 

[Ed from AZ]

Many thanks! I just tested it on a 2300 row table, with my search
criterion in the next to last row - got my return in a blink!!

Very gratefully,
Ed