# Coding 'Find All' Problem

#### egibberate

##### Board Regular
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;

Code:
``````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

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.

Last edited:

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Where are the values being returned to? A new sheet? Another column?

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.

Hi,

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

Replies
3
Views
1K
Replies
7
Views
2K
Replies
1
Views
926
Replies
6
Views
405
Replies
4
Views
2K

1,202,981
Messages
6,052,900
Members
444,610
Latest member
dodong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back