From: Mike H on
Christina.

Copy my code then go to your code and select all the lines from

Do

to

Loop

The pres and hold the CTRL key and tap V to paste my code over yours
--
Mike

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


"Christina" wrote:

> Thanks. I am sorry I need step by step instruction. This Do Loop is part
> of the macro. After the first part is run, then it moves into the LOOP.
>
> So where do I put your codes. Do I start with DO, then copy your codes.
>
> Sorry for more questions, I am very basic at this.
>
> THanks
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Try this
> >
> > Sub nn()
> > Dim LastRow As Long
> > Dim MyRange As Range
> > LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
> > Set MyRange = Range("F1:F" & LastRow)
> > For Each c In MyRange
> > If UCase(c.Value) = "VENDOR ID" Then c.ClearContents
> > Next
> > End Sub
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "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
From: Christina on
When I copy the code it all comes up in red, ANd when I try to run it it
says
Compile Error
Expected End Sub


Thanks
Cristina

"Mike H" wrote:

> Christina.
>
> Copy my code then go to your code and select all the lines from
>
> Do
>
> to
>
> Loop
>
> The pres and hold the CTRL key and tap V to paste my code over yours
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Christina" wrote:
>
> > Thanks. I am sorry I need step by step instruction. This Do Loop is part
> > of the macro. After the first part is run, then it moves into the LOOP.
> >
> > So where do I put your codes. Do I start with DO, then copy your codes.
> >
> > Sorry for more questions, I am very basic at this.
> >
> > THanks
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > Try this
> > >
> > > Sub nn()
> > > Dim LastRow As Long
> > > Dim MyRange As Range
> > > LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
> > > Set MyRange = Range("F1:F" & LastRow)
> > > For Each c In MyRange
> > > If UCase(c.Value) = "VENDOR ID" Then c.ClearContents
> > > Next
> > > End Sub
> > > --
> > > Mike
> > >
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > > introduces the fewest assumptions while still sufficiently answering the
> > > question.
> > >
> > >
> > > "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
From: Chip Pearson on
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: L. Howard Kittle on
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: Christina on
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
> .
>