Coding 'Find All' Problem

[egibberate]

Hi,

Ultimate Goal: User inputs a serial number into a textbox then clicks a cmdButton & the code behind the button returns every instance of that serial number from column A [along with the rest of the data from the other cells in each row]
Something along these lines;

ser number date In date Out time In time Out who etc
XYZ123 01/09/14 n/a 6:00 n/a Tom
XYZ123 n/a 04/09/14 n/a 14:00 Pete

I have this code which when tested 'selects' the cells that contain the requested serial number in Column A.
I can see when I step through it that 'FoundCell.Address' captures the cell addresses [in my test case A3 and A10 and A15] but I can't work out how to capture and assign those cell names to variables so that I can use them. Any guidance would be very much appreciated indeed;

Private Sub CommandButton1_Click()


'find one or more cells in column A that contain a specified value


Dim SerialNumber As String, FirstFound As String
SerialNumber = UserForm1.TextBox1.Value
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range


    Set myRange = ActiveSheet.UsedRange
    Set LastCell = myRange.Cells(myRange.Cells.Count)
    Set FoundCell = myRange.Find(what:=SerialNumber, after:=LastCell)


'Test to see if anything was found
    
    If Not FoundCell Is Nothing Then
             FirstFound = FoundCell.Address
             Else
             GoTo NothingFound
    End If


Set rng = FoundCell


'Loop until cycled through all finds
  
    Do Until FoundCell Is Nothing
        'Find next cell with the required SerialNumber
             Set FoundCell = myRange.FindNext(after:=FoundCell)
                'Add found cell to rng range variable
                 Set rng = Union(rng, FoundCell)
                    'Test to see if cycled through to first found cell
        If FoundCell.Address = FirstFound Then Exit Do
    Loop


'Select cells containing Find value
  rng.Select


Exit Sub


'Error Handler
NothingFound:
  MsgBox "No values were found in this worksheet"




End Sub

Thank you for taking the time to read my post.

 

[NeonRedSharpie]

Where are the values being returned to? A new sheet? Another column?

 

[egibberate]

Hi NeonRedSharpie,

Thank you for taking the time to read my post.
It was my intention to return the values to Notepad eventually, but, initially a msgBox.
There'll never be any more than 5 or 6 rows returned from the search.

thanks again.

 

[egibberate]

Hi,

I'd like to close this thread. I found the perfect solution. Chip Pearson's FindAll function.