problems about range.findnext method

[Elainaz]

Hi all,
I'm new to vba and trying to use it dealing the following task that confusing me:
I have a series code number and want to search them in this table.

columnA	Code Number	columnB	columnC
information1	aaa	information1	information1
information2	bbb	information2	information2
information3	aaa	information3	information3
information4	ccc	information4	information4
information5	ddd	information5	information5
information6	bbb	information6	information6

<tbody>
</tbody>

As you may see, the code numbers might appear more than once. I need it to return every row the target code number's in and make a new table in another worksheet.
Hope that I described this problem clearly because I'm not good at English actually.
I'm using "for each" and "range.findnext" to write the vba and here is the code I write:

Sub fundsearching()
Dim rng2 As Range, rng3 As Range, fdrng As Range, row1 As Long, firstaddress As String
For Each rng3 In Sheet3.Range("A2:A59")    'a list of code numbers I need to look for
    Set rng2 = Sheet2.Range("B2:B7383")     'the database where all the information are, just like the table above
    Set fdrng = rng2.Find(rng3.Value, , xlValues, xlWhole)
    If Not fdrng Is Nothing Then
        Set firstaddress = fdrng.Address         'the system pops out an error here
        Do
            row1 = Sheet3.[D1].Value       'I used a counta function here in this cell counting the numbers of non-blank rows in the new table, so that I could write new information on the first blank row. I used [A62200].end(xlUP) but it didn't work out so I used this stupid way.
            Sheet1.Cells(row1, "A").Value = rng3.Value
            Sheet1.Cells(row1, "B").Value = Sheet2.Cells(fdrng.Row, "C").Value
            Sheet1.Cells(row1, "C").Value = Sheet2.Cells(fdrng.Row, "A").Value
            Sheet1.Range("D" & row1 & ":M" & row1).Value = Sheet2.Range("D" & fdrng.Row & ":M" & fdrng.Row).Value
            Set fdrng = rng2.FindNext(fdrng)
        Loop While fdrng.Address <> firstaddress
    End If
Next rng3
End Sub

Could anyone help me fixing this please? I'd really appreciate that.

 

[steve the fish]

You dont Set a string so just:

firstaddress = fdrng.Address

 

[Yongle]

Set is used with objects like sheets, ranges, shapes, tables etc
variable firstaddress is expecting a string not an object

Set is not required

firstaddress = fdrng.Address

 

[Elainaz]

Many thanks! It works now!

 

[Elainaz]

Thanks for your kind explanation!!!

 

[Yongle]

thanks for your feedback