Solved: Using find to put values in a matrix

[WebGuy]

Hello!

I need some help to find values in a range and put them in a matrix.

First a breif explaination of what i want to do:

I have a spreadsheet full of logs (8 columns). I want to search the textstrings in column 8. if i find a "Keyword" i want to copy that entire row into a matrix (Logmatrix(100,7)) so that each column value gets its own position in the matrix. I have attempted to try this, but since i have no previous experience in using find i am having trouble understanding how it works.

Here is a piexe of my code:

[VBA]
Set Foundcell = Range("H2:H65536").Find(What:=SearchString)
Do Until Foundcell Is Nothing
ActiveCell.Offset(0, -7).Select
i = 0
For i = 0 To 7
LogMatrix(j, i) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
i = i + 1
Next i
j = j + 1
Set Foundcell = Range("H2:H65536").FindNext
Loop
[/VBA]

I hope you can help me.
Thank you !

[Killian]

Hi,

I've assummed that you'll eventually want to search for a list of string with this, so I've arranged the code for that.

The "main" routine just defines a list of strings to search for (I've used an array, it could easily be a range from Excel) and calls the "FindAndAddToMatrix" for each one.

"FindAndAddToMatrix" is basically your Find loop with a couple of additions:
Save the address of Find then loop FindNext until your back around to the firstaddress.
A function to get the next free index in the array instead of keeping a counter.
[VBA]'module level declaration of array
Dim Logmatrix(1 To 100, 1 To 8) As String

'###############
Sub main()
'loop thru a list of strings
Dim a As Variant
Dim i As Long

a = Array("abc", "teststring", "xyz")
For i = LBound(a) To UBound(a)
FindAndAddToMatrix a(i)
Next i

End Sub

'###############
Sub FindAndAddToMatrix(ByVal strSearch As String)

Dim rngToSearch As Range
Dim c As Range
Dim firstAddress As String
Dim i As Long, j As Long

Set rngToSearch = ActiveSheet.Columns(8)

Set c = rngToSearch.Find(What:=strSearch, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
j = LogmatrixIndex
If j > 0 Then
For i = 0 To 7
Logmatrix(j, i + 1) = c.Offset(0, 0 - i).Value
Next i
Set c = rngToSearch.FindNext(c)
Else
MsgBox "Unable to allocate free array index"
Exit Do
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End Sub

'###############
Function LogmatrixIndex() As Long
'returns first free index in Logmatrix
Dim i As Long
For i = 1 To 100
If Logmatrix(i, 1) = "" Then
LogmatrixIndex = i
Exit For
End If
Next i
End Function[/VBA]

[WebGuy]

Thanks a lot !
I think that will do it !
I'll try it out first thing tomorrow morning.