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

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Hi wiseone. Maybe something like this will get you started. Adjust the sheet name and desired data columns to suit. HTH. Dave
Code:
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top