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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]columnA[/TD]
[TD]Code Number[/TD]
[TD]columnB[/TD]
[TD]columnC[/TD]
[/TR]
[TR]
[TD]information1[/TD]
[TD]aaa[/TD]
[TD]information1[/TD]
[TD]information1[/TD]
[/TR]
[TR]
[TD]information2[/TD]
[TD]bbb[/TD]
[TD]information2[/TD]
[TD]information2[/TD]
[/TR]
[TR]
[TD]information3[/TD]
[TD]aaa[/TD]
[TD]information3[/TD]
[TD]information3[/TD]
[/TR]
[TR]
[TD]information4[/TD]
[TD]ccc[/TD]
[TD]information4[/TD]
[TD]information4[/TD]
[/TR]
[TR]
[TD]information5[/TD]
[TD]ddd[/TD]
[TD]information5[/TD]
[TD]information5[/TD]
[/TR]
[TR]
[TD]information6[/TD]
[TD]bbb[/TD]
[TD]information6[/TD]
[TD]information6[/TD]
[/TR]
</tbody>[/TABLE]

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