Hi,


Thanks a lot, Its just what I wanted



Greetings,

Farmer



On Mon, 11 Oct 2004 19:38:30 -0500, Dave Peterson
wrote:

There's a nice example in VBA's help for .find that shows how to search until
you loop around to the first found cell.

Option Explicit

Private Sub CommandButton2_Click()

Dim sh As Worksheet
Dim FoundCell As Range
Dim FirstAddress As String
Dim Counter As Long

Set sh = ThisWorkbook.Worksheets("sheet1")

Counter = 0
Me.ListBox1.Clear

With sh.Range("a:a") 'what was selected??
Set FoundCell = .Find(What:="cash", After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
Me.ListBox1.AddItem FoundCell.Value
Counter = Counter + 1
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With

End Sub

I'm not sure what Counter was used for and I figured the listbox was on the same
worksheet as the commandbutton2.

And watchout. SearchFormat was added in xl2002. If you share your workbook
with people who use xl2k or before, remove this portion.




farmer wrote:

Hi,

I've got a simple problem (I think)

I would like to search within a selection using the find method. Then
transfer the found cells to a listbox.

The problem is that the code I'm using loops a "few times to many".
Does anyone know what's wrong with it?

Here's the code

Private Sub CommandButton2_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("sheet1")

sh.UsedRange.Activate
cntend = LastRow(sh)

Selection.Find(What:="cash", After:=ActiveCell, LookIn:=xlValues,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Select

counter = 1
Do While counter < cntend

Cells.FindNext(After:=ActiveCell).Activate
ListBox1.AddItem ActiveCell.Value
counter = counter + 1

Loop

End Sub

Help Appreciated,

Farmer