Create Macro to Find Text String Within a Range and Return All Data in the Same Row

[JosephALin]

Good morning, I am building a macro that will find a certain text from cell A2 sheet named Committees withing the range of P2:CP5000 in sheet called database, and return data of column A:O in the same rows from all the rows that contain this text string, and print it out on the sheet called reports. Here is what I have done, but it doesn't work, I couldn't figure out what is wrong. Please check for me.

Sub Macro1()'
'
' Macro1 Macro
'


Dim r1 As Range, r2 As Range, r3 As Range
Dim rw1 As Long, LastRow As Long, i As Long


Set r2 = Sheets("Committees").Range("A2")
Set r1 = Sheets("Database").Range("P2:CO5000")
Set r3 = Sheets("Reports").Range("F2")


rw1 = r1.Find(What:=r2.Value, After:=r1(1)).Row
LastRow = Cells(r1.Rows, Count, "U").End(xlUp).Row


For i = 1 To LastRow
If Not rw1 Is Nothing Then
   Do Until rw1 Is Nothing
      Sheets("Database").Range("A" & rw1 & ":O" & rw1).Copy r3
      Set rw1 = .FindNext(rw1)
   Loop
End If
Set rw1 = Nothing
Next
End Sub

Thanks in advance!

 

[Rhodie72]

Firstly, I'd like to know what errors are springing up.
Secondly, you are searching through 5000 rows and 93 columns. Surely the information you are searching for is in just a few columns and not in all 93 columns? If you can limit the search to fewer columns, you shorten the search time. If you load the range into memory as an array, then operate on the array instead of the sheet, you will see a massive speed improvement (up to around 18 to fold shorter!)
Thirdly, all your results are being sent to the same cell: Sheets("Reports").Range("F2")

This is no good. it would be better to store them in an array and drop them onto a range with an offset on the report sheet without activating that sheet. Since I don't have a vast randomised dataset to
work with as you do, it is going to be tough to get something working without answers to these questions. Hope this helps.

 

[Jon von der Heyden]

Good morning, I am building a macro that will find a certain text from cell A2 sheet named Committees withing the range of P2:CP5000 in sheet called database, and return data of column A:O in the same rows from all the rows that contain this text string, and print it out on the sheet called reports. Here is what I have done, but it doesn't work, I couldn't figure out what is wrong. Please check for me.

I can recommend an entirely different approach. Are you familiar with Advanced Filter?

I reckon you can do a one-hit copy and paste to the report sheet. Advanced Filter allows you to use a formula to define the criteria, and to drop the filtered results to a different range. So lets assume that database!CR:CR is spare. You could put this formula into CR2:

=COUNTIF(P2:CP2,'Committees '!$A$2)>0

Then you can go to the report sheet and invoke Advanced Filter, following these steps:

1. Go to the report sheet

2. Go Advanced Filter, info as follows:
Filter Range: database!P1:CP5000 (assume row 1 has headers?)
Criteria Range: database!CR1:CR2 (make sure that CR1 is blank)
Copy to Range: F2

3. Click OK