First, I wouldn't use this. I think there are not enough checks to make sure
that the data matches what you expect.

And I'm guessing that you expect that "vendor id" header cell, more than one
non-empty "detail" cell, a gap before the next header cell and more detail
cells.

I'd want to check that I had some non-empty details and make sure that there was
a gap between the header and the previous details.


But I _think_ that this does what your code did.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim myCell As Range
Dim RngToFill As Range

Set wks = Worksheets("Sheet1")

With wks
Do
With .Range("F:f")
Set FoundCell = .Cells.Find(What:="Vendor ID", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

If FoundCell Is Nothing Then
'done, get out
Exit Do
End If

Set myCell = FoundCell.Offset(1, 0)
Set RngToFill = .Range(myCell, myCell.End(xlDown))

FoundCell.ClearContents

myCell.Copy _
Destination:=RngToFill

Loop
End With

End Sub

But it still scares me!



Christina wrote:

Actually I would need it to Exit when there are no more cells with "Vendor ID"

Do

Range("F:F").Find(What:="Vendor ID", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
ActiveCell.ClearContents
ActiveCell.Offset(1, 0).Select

Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

Loop

Thanks

"Mike H" wrote:

Christina,

Post your loop and someone will help
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Christina" wrote:

I need help with a loop. After looping through the procedure, when the
active cell is empty I want to exit the loop. Would you please give me the
codes to do that.


Thanks


Cristina

--

Dave Peterson