JB

Try this modification

Sub FindStuff()

Dim FndRng As Range
Dim FirstAdd As String
Dim i As Long

Set FndRng = Sheets("SCIT").Cells.Find( _
what:=ActiveSheet.Range("g9").Value, _
after:=Sheets("SCIT").Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

'If value is not found, FndRng will be Nothing
If Not FndRng Is Nothing Then

'Store the first address found
FirstAdd = FndRng.Address
i = 0

'Start the loop
Do

'Write to the sheet
Sheets("FINDER").Range("g34").Offset(i, 0).Value = FndRng.Value

'Find the next occurrence
Set FndRng = Sheets("SCIT").Cells.Find( _
what:=ActiveSheet.Range("g9").Value, _
after:=FndRng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

'increment the offset
i = i + 1

'Stop looping when it cycles back to the first one
Loop Until FndRng.Address = FirstAdd

End If

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"-JB-" wrote in message
...
Thanks Dick. It works! You're a great guy!

You solved my biggest problem. Can the macro be amended to continue
searching the database worksheet for more possible matches, and send
the results to "FINDER" worksheet, F40, F41, F42, F43 etc?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/