From: Christina on
Please bear with me. I am so basic. Would you please make adjustments to this.
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
ActiveCell.Offset(1, 0).Select

Loop

End Sub



Thanks
"L. Howard Kittle" wrote:

> At the very end of the code you will need to enter this required item:
>
> End Sub
>
> Enter it just below the last red line of code. So you will have something
> like this
>
>
> Sub MyMacro()
>
> 'all the code you want including Mike H's.
>
> End Sub
>
> If you have say only two consecutive lines of code that are red after a copy
> and paste, then it means the code line has wrapped and you need to have a
> line break OR put all that line of code on a single line.
>
> To fix a red line wrap, go to the end of the upper red line of code and from
> at very end do a SPACE and then an _ (underscore) then arrow up or down off
> the line. Usually that will correct it but sometimes the line of code will
> wrap at a place that will NOT accept a line break. If that happens best to
> just put it all in one line and live with it being way off the screen, OR
> you can put it all on one line and try different parts of the line to put
> the space and underscore and hit enter. If you pick a proper place then the
> code will compile and all the red will go away. You may have to try a few
> different spots, when it does not compile go to the end of the upper red
> line and hit delete to bring the bottom line back in place. (May have to
> hit delete several times to get rid of the large space created when it
> wrapped.
>
> HTH
> Regards,
> Howard
>
> "Christina" <Christina(a)discussions.microsoft.com> wrote in message
> news:5A3C8C0C-DF20-4066-920B-DEE4637B0B9E(a)microsoft.com...
> >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
>
>
> .
>
From: Dave Peterson on
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
From: L. Howard Kittle on
Hi Christina,

I ain't figuring out what you want to do. If you want, send me a sample
workbook and detailed instructions of what you want to happen, where you
want it to happen and the such.

You have a lot of .Select in the code offered and you seldom need to select
stuff to get stuff done.

L&H&Kittle(a)comcast.net

Remove the & charachters.

Regards,
Howard

"Christina" <Christina(a)discussions.microsoft.com> wrote in message
news:3D4C7A2B-47EE-4CAF-8E2F-287B923E3AAE(a)microsoft.com...
> Where do I put that , or what do I replace in my loo
>
> My Do starts after some other functions, then DO......LOOP then End Sub
> for
> the enture Macro. This is the loop:
>
> 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
> ActiveCell.Offset(1, 0).Select
>
> Loop
>
> The Data that needs to be copied is in the cell after the cell with Text
> ---Vendor Id.
>
>
> Thanks
>
> "Chip Pearson" wrote:
>
>> Try something like the following:
>>
>> Sub AAA()
>> Dim R As Range
>> Set R = ActiveCell
>> Do Until R.Value = vbNullString
>> Debug.Print "do something with R"
>> Set R = R(2, 1) ' move down
>> Loop
>> End Sub
>>
>> Note that the Active Cell is not changed within the loop. ActiveCell
>> will point to the first cell during the entire looping process. You
>> use the R variable to access each cell within the loop. If you really
>> need to change which cell is active (it is almost never necessary),
>> use
>>
>> R.Select
>>
>> on a new line just below the Do Until line.
>>
>> Cordially,
>> Chip Pearson
>> Microsoft Most Valuable Professional,
>> Excel, 1998 - 2010
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>>
>>
>> On Thu, 18 Mar 2010 07:56:06 -0700, Christina
>> <Christina(a)discussions.microsoft.com> 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
>> .
>>


From: Christina on
PLEASE help.

I have tried everything, and have not been able to get this to work. I
Would you please send me the codes that I need to replace the ones I have.
The one I have works, does the loop copies all the cells needed, but does not
end. I brings up a dialog box and I have to end it myself. I really would
need to finish this today.


Thanks

"Christina" wrote:

> Please bear with me. I am so basic. Would you please make adjustments to this.
> 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
> ActiveCell.Offset(1, 0).Select
>
> Loop
>
> End Sub
>
>
>
> Thanks
> "L. Howard Kittle" wrote:
>
> > At the very end of the code you will need to enter this required item:
> >
> > End Sub
> >
> > Enter it just below the last red line of code. So you will have something
> > like this
> >
> >
> > Sub MyMacro()
> >
> > 'all the code you want including Mike H's.
> >
> > End Sub
> >
> > If you have say only two consecutive lines of code that are red after a copy
> > and paste, then it means the code line has wrapped and you need to have a
> > line break OR put all that line of code on a single line.
> >
> > To fix a red line wrap, go to the end of the upper red line of code and from
> > at very end do a SPACE and then an _ (underscore) then arrow up or down off
> > the line. Usually that will correct it but sometimes the line of code will
> > wrap at a place that will NOT accept a line break. If that happens best to
> > just put it all in one line and live with it being way off the screen, OR
> > you can put it all on one line and try different parts of the line to put
> > the space and underscore and hit enter. If you pick a proper place then the
> > code will compile and all the red will go away. You may have to try a few
> > different spots, when it does not compile go to the end of the upper red
> > line and hit delete to bring the bottom line back in place. (May have to
> > hit delete several times to get rid of the large space created when it
> > wrapped.
> >
> > HTH
> > Regards,
> > Howard
> >
> > "Christina" <Christina(a)discussions.microsoft.com> wrote in message
> > news:5A3C8C0C-DF20-4066-920B-DEE4637B0B9E(a)microsoft.com...
> > >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
> >
> >
> > .
> >
From: L. Howard Kittle on
See my reply to send me a sample workbook, dated 2/18/201.

Regards,
Howard

"Christina" <Christina(a)discussions.microsoft.com> wrote in message
news:F5F1100D-10CF-4660-9C3A-8472042882D3(a)microsoft.com...
> PLEASE help.
>
> I have tried everything, and have not been able to get this to work. I
> Would you please send me the codes that I need to replace the ones I have.
> The one I have works, does the loop copies all the cells needed, but does
> not
> end. I brings up a dialog box and I have to end it myself. I really
> would
> need to finish this today.
>
>
> Thanks
>
> "Christina" wrote:
>
>> Please bear with me. I am so basic. Would you please make adjustments to
>> this.
>> 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
>> ActiveCell.Offset(1, 0).Select
>>
>> Loop
>>
>> End Sub
>>
>>
>>
>> Thanks
>> "L. Howard Kittle" wrote:
>>
>> > At the very end of the code you will need to enter this required item:
>> >
>> > End Sub
>> >
>> > Enter it just below the last red line of code. So you will have
>> > something
>> > like this
>> >
>> >
>> > Sub MyMacro()
>> >
>> > 'all the code you want including Mike H's.
>> >
>> > End Sub
>> >
>> > If you have say only two consecutive lines of code that are red after a
>> > copy
>> > and paste, then it means the code line has wrapped and you need to have
>> > a
>> > line break OR put all that line of code on a single line.
>> >
>> > To fix a red line wrap, go to the end of the upper red line of code and
>> > from
>> > at very end do a SPACE and then an _ (underscore) then arrow up or down
>> > off
>> > the line. Usually that will correct it but sometimes the line of code
>> > will
>> > wrap at a place that will NOT accept a line break. If that happens
>> > best to
>> > just put it all in one line and live with it being way off the screen,
>> > OR
>> > you can put it all on one line and try different parts of the line to
>> > put
>> > the space and underscore and hit enter. If you pick a proper place
>> > then the
>> > code will compile and all the red will go away. You may have to try a
>> > few
>> > different spots, when it does not compile go to the end of the upper
>> > red
>> > line and hit delete to bring the bottom line back in place. (May have
>> > to
>> > hit delete several times to get rid of the large space created when it
>> > wrapped.
>> >
>> > HTH
>> > Regards,
>> > Howard
>> >
>> > "Christina" <Christina(a)discussions.microsoft.com> wrote in message
>> > news:5A3C8C0C-DF20-4066-920B-DEE4637B0B9E(a)microsoft.com...
>> > >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
>> >
>> >
>> > .
>> >