Below is my code, I've stretched my brain to its limit on this so I'm wandering if any one can help me out?
I'm using the ‘find’ and ‘findnext’ function to locate a record from a table then list the row on another sheet. My code only finds two records, so I need some sort of loop which keeps searching until all matching records are found!
Sub Find()
Dim R
Dim F
Dim Na
Dim F2
Na = InputBox("Enter Name")
On Error GoTo 1
F = Range("Table").Find(Na).Address
F2 = Range("Table").FindNext(After:=Range(F)).Address
R = Sheets("Sheet2").UsedRange.Rows.Count + 1
If Sheets("Sheet2").Range("A1") = "" Then
R = 1
End If
With Sheets("Sheet2").Cells(R, 1)
.Value = Sheets("Sheet1").Range(F)
.Offset(0, 1) = Sheets("Sheet1").Range(F).Offset(0, 1)
.Offset(0, 2) = Sheets("Sheet1").Range(F).Offset(0, 2)
End With
If F2 > F Then
With Sheets("Sheet2").Cells(R, 1)
.Offset(1, 0) = Range("Table").FindNext(After:=Range(F))
.Offset(1, 1) = Range("Table").FindNext(After:=Range(F)).Offset(0, 1)
.Offset(1, 2) = Range("Table").FindNext(After:=Range(F)).Offset(0, 2)
End With
End If
Exit Sub
1
MsgBox ("Not found")
End Sub
Thanks in advance :D
Ryan A UK
I'm using the ‘find’ and ‘findnext’ function to locate a record from a table then list the row on another sheet. My code only finds two records, so I need some sort of loop which keeps searching until all matching records are found!
Sub Find()
Dim R
Dim F
Dim Na
Dim F2
Na = InputBox("Enter Name")
On Error GoTo 1
F = Range("Table").Find(Na).Address
F2 = Range("Table").FindNext(After:=Range(F)).Address
R = Sheets("Sheet2").UsedRange.Rows.Count + 1
If Sheets("Sheet2").Range("A1") = "" Then
R = 1
End If
With Sheets("Sheet2").Cells(R, 1)
.Value = Sheets("Sheet1").Range(F)
.Offset(0, 1) = Sheets("Sheet1").Range(F).Offset(0, 1)
.Offset(0, 2) = Sheets("Sheet1").Range(F).Offset(0, 2)
End With
If F2 > F Then
With Sheets("Sheet2").Cells(R, 1)
.Offset(1, 0) = Range("Table").FindNext(After:=Range(F))
.Offset(1, 1) = Range("Table").FindNext(After:=Range(F)).Offset(0, 1)
.Offset(1, 2) = Range("Table").FindNext(After:=Range(F)).Offset(0, 2)
End With
End If
Exit Sub
1
MsgBox ("Not found")
End Sub
Thanks in advance :D
Ryan A UK