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.
<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:
Could anyone help me fixing this please? I'd really appreciate that.
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:
Rich (BB code):
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.
Last edited by a moderator: