Create Macro to Find Text String Within a Range and Return Data From Matched Rows

Question

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 started from cell F2 on the sheet called reports. Here is what I have done based on some suggestions. However, it is not returning expected values, it copies data from column A in Database to F:T in reports. Also I think the loop is not working since it won't stop after the last row in range r1.

Sub Macro1()

Dim r1 As Range, r2 As Range, r3 As Range
Dim rw1 As Long
Dim tmpRow As Long

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

rw1 = 0
rw1 = r1.Find(What:=r2.Value, After:=r1(1)).Row

Do While rw1 <> 0
r3.Value = Sheets("Database").Range("A" & rw1 & ":O" & rw1).Value
tmpRow = tmpRow + 1
Set r3 = ThisWorkbook.Sheets("Reports").Range("F" & tmpRow & ":T" & tmpRow)
rw1 = 0
rw1 = r1.FindNext().Row
Loop
End Sub

Thanks in advance!

Sun · Accepted Answer · 2016-05-19 09:08:22Z

1

Final Edit:

I forgot to check for whether that particular cell had already been found (now with FirstAddress). The infinite loop was due to the code finding the same entries over and over.

I tested the following code, and it works for me.

Sub joseph()

Dim awb As Workbook
Dim cm, db, rp As Worksheet 'committees, database, reports

Dim tmpRng As Range
Dim firstAddress As String
Dim tmpRow As Integer

Dim r As Integer

Dim searchValue As String


    Set awb = ThisWorkbook

    With awb
        Set cm = .Worksheets("Committees")
        Set db = .Worksheets("Database")
        Set rp = .Worksheets("Reports")
    End With

    searchValue = cm.Range("A2").Value

    tmpRow = 0
    r = 2

    With db

        Set tmpRng = .Range("P2:CP5000").Find(searchValue, _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)

        If Not tmpRng Is Nothing Then

            firstAddress = tmpRng.Address

            Do
                tmpRow = tmpRng.Row
                rp.Range("F" & r & ":T" & r).Value = .Range("A" & tmpRow & ":O" & tmpRow).Value
                r = r + 1
                Set tmpRng = .Range("P2:CP5000").FindNext(tmpRng)
            Loop While Not tmpRng Is Nothing And tmpRng.Address <> firstAddress

        End If

    End With

End Sub

Initial replies and edits:

Try changing this line

Set rw1 = .FindNext(rw1)

to

rw1 = r1.FindNext().Row

rw1 is not a Range, therefore type mismatch. Let me know if this works.

Edit: also change this line:

If Not rw1 Is Nothing Then

to

if rw1 <> 0 then

and add this line

rw1 = 0

before this line:

rw1 = r1.Find(What:=r2.Value, After:=r1(1)).Row

Edit2: about the for loop and updating r3 If the text string exists multiple times, you must keep the for-loop, and you can update the r3 range by declaring a row variable, e.g.

Dim tmpRow = 2 as integer

and then inside your for-loop you write before rw1 = 0

tmpRow = tmpRow + 1
set r3 = thisWorkbook.Sheets("Reports").Range("F" & tmprow & ":T" & tmprow)

edited May 19, 2016 at 9:08

answered May 18, 2016 at 13:43

Sun

7724 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Joseph Lin Over a year ago

It is still saying rw1 is a mismatch

Joseph Lin Over a year ago

Thank you for responding me so quick. now it is saying the LastRow = Cells Sentence is a type mismatch now.

Sun Over a year ago

What is it supposed to be the lastrow of? Sheet Database? Also, what is Count?

Joseph Lin Over a year ago

Yes, it supposed to read until the last row of database within column P to CO, I realized the contradiction of r2 and lastRow there. The Count is just something I copy from the Internet, I am very new to VBA.

Sun Over a year ago

I guess in your case you can just have lastrow = 5000 then, and also delete the row Loop

|

Collectives™ on Stack Overflow

Create Macro to Find Text String Within a Range and Return Data From Matched Rows

1 Answer 1

22 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

22 Comments

Your Answer

Sign up or log in

Post as a guest

Related