find problem

[jon]

Hi i got some advice from Chip Pearson on Friday on the
find method...

Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
If Not FoundCell Is Nothing Then
' Find was successful, do something with FoundCell
Else
' Not Found
End If

....

This still doesn't seem to work when using it up to 12
times in a loop. is there anything you have to put in in
this situation. I'm sorry if this is very simple, but i
think i do need it spelling out for me. I need to find
the beginning and end of a section of data, see below...


apple
apple
apple
apple
pear
pear
pear
orange
banana
banana
banana

.... and export each section to a different file, which i
can do. I'm sorry to be annoying, but it's on a deadline
and i'm tearing my hair out.

 

[jon]

don't worry, i was being stupid, i forgot to take
the .activate off the end of the find command. So sorry
to bother everyone. Thanks for the previous help Chip.

 

[Frank Kabel]

Hi
try something like the following:
Sub foo()
Dim firstAddress
Dim FoundCell As Range
Dim search_rng As Range
Set seaarch_rng = Selection
Set FoundCell = search_rng.Find(....)
Dim firstRow
Dim lastRow

If Not FoundCell Is Nothing Then
firstAddress = FoundCell.Address
firstRow = FoundCell.Row
Do
lastRow = FoundCell.Row
Set FoundCell = search_rng.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And
FoundCell.Address <> firstAddress
Else
' Not Found
End If
End Sub