Macro to Find String in a column and store values from those rows in Array

[wiseone]

Hello all,

I'd like to create a macro to find a value (text) in a column where multiple cells in this column may contain this value. For those rows found, I'd like to store certain cell's information from that row in an array. Can anyone advise how to do this?

I have a named range on the header cell of the column in question.

The search column will grow over time, so not sure if its best to search the entire column or if there is a better way to limit the search ? ....thoughts?

Thanks in advance!

 

[NdNoviceHlp]

Hi wiseone. Maybe something like this will get you started. Adjust the sheet name and desired data columns to suit. HTH. Dave

Function LoadArray(Rng As Range, SearchWrd As String) As Variant
Dim Arr() As Variant, LastRow As Integer, Cnt As Integer, ArCnt As Integer
With Sheets("sheet1")
LastRow = .Cells(.Rows.Count, Rng.Column).End(xlUp).Row
For Cnt = 2 To LastRow
If LCase(.Cells(Cnt, Rng.Column)) = LCase(SearchWrd) Then
ArCnt = ArCnt + 1
ReDim Preserve Arr(ArCnt)
Arr(ArCnt - 1) = Cnt
End If
Next Cnt
End With
LoadArray = Arr
End Function

Private Sub test()
Dim Arr As Variant, Cnt As Integer
'Search range "bart" for text "bob"
Arr = LoadArray(Application.Range("bart"), "bob")
'output row results
For Cnt = LBound(Arr) To UBound(Arr) - 1
With Sheets("sheet1")
MsgBox .Cells(Arr(Cnt), "A")
MsgBox .Cells(Arr(Cnt), "B")
MsgBox .Cells(Arr(Cnt), "C")
End With
Next Cnt
End Sub

To operate, adjust the sheet name and desired output columns. Change the named range and desired search word to suit. Run the test sub.

 

[wiseone]

Thanks I will test this out now.

 

[wiseone]

Works like a charm. Thanks a million.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Be safe. Dave