VBA Loop until certain cell!

[[email protected]]

Hi,

I am trying to look for the first positive number in a given colomn,
but I want the loop to stop looping at a certain row and stay in a
given range.

Here is my current code:

Do
Selection.End(xlUp).Select
Loop Until ActiveCell > 0

How can I have the loop to stop looking up say at row 100?

Many thanks,

Gaetan

[Bob Phillips]

Do
Selection.End(xlUp).Select
If Selection.Row < 20 Then Exit Do
Loop Until ActiveCell > 0


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am trying to look for the first positive number in a given colomn,
> but I want the loop to stop looping at a certain row and stay in a
> given range.
>
> Here is my current code:
>
> Do
> Selection.End(xlUp).Select
> Loop Until ActiveCell > 0
>
> How can I have the loop to stop looking up say at row 100?
>
> Many thanks,
>
> Gaetan
>

[[email protected]]

Thanks a lot for your answer Bob, but it still doesn't work and keeps
taking numbers above the row 20.
To be more accurate, as you can see I would like the code to start in a
cell (here B24) and look upside until cell B5, but not above this cell
(the code you gave me keeps going above), and from then go to the left
and copy the number to be pasted elsewhere.
So that if there is nothing (or a negative number) between rows B24 and
B5, it will return "" in B27, and not go for anything above.

here is the whole code:

Sub test5()

Range("B24").Select

Do
Selection.End(xlUp).Select
If Selection.Row < 5 Then Exit Do
Loop Until ActiveCell > 0

Selection.End(xlToLeft).Select

ActiveCell.Copy
Range("B27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub


Thanks very much for your help!

Gaetan

Bob Phillips wrote:

> Do
> Selection.End(xlUp).Select
> If Selection.Row < 20 Then Exit Do
> Loop Until ActiveCell > 0
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am trying to look for the first positive number in a given colomn,
> > but I want the loop to stop looping at a certain row and stay in a
> > given range.
> >
> > Here is my current code:
> >
> > Do
> > Selection.End(xlUp).Select
> > Loop Until ActiveCell > 0
> >
> > How can I have the loop to stop looking up say at row 100?
> >
> > Many thanks,
> >
> > Gaetan
> >

[Bob Phillips]

What does your data look like,and where are you expecting to stop?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Thanks a lot for your answer Bob, but it still doesn't work and keeps
> taking numbers above the row 20.
> To be more accurate, as you can see I would like the code to start in a
> cell (here B24) and look upside until cell B5, but not above this cell
> (the code you gave me keeps going above), and from then go to the left
> and copy the number to be pasted elsewhere.
> So that if there is nothing (or a negative number) between rows B24 and
> B5, it will return "" in B27, and not go for anything above.
>
> here is the whole code:
>
> Sub test5()
>
> Range("B24").Select
>
> Do
> Selection.End(xlUp).Select
> If Selection.Row < 5 Then Exit Do
> Loop Until ActiveCell > 0
>
> Selection.End(xlToLeft).Select
>
> ActiveCell.Copy
> Range("B27").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
>
> End Sub
>
>
> Thanks very much for your help!
>
> Gaetan
>
> Bob Phillips wrote:
>
> > Do
> > Selection.End(xlUp).Select
> > If Selection.Row < 20 Then Exit Do
> > Loop Until ActiveCell > 0
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > I am trying to look for the first positive number in a given colomn,
> > > but I want the loop to stop looping at a certain row and stay in a
> > > given range.
> > >
> > > Here is my current code:
> > >
> > > Do
> > > Selection.End(xlUp).Select
> > > Loop Until ActiveCell > 0
> > >
> > > How can I have the loop to stop looking up say at row 100?
> > >
> > > Many thanks,
> > >
> > > Gaetan
> > >
>

[[email protected]]

15/02/2006 12125
16/02/2006 211254
17/02/2006 14212
18/02/2006

STOP
17/08/2006
18/08/2006
19/08/2006
20/08/2006
21/08/2006
22/08/2006
23/08/2006 -132113
24/08/2006

There is in this example no number that should be returned in the range
selected (B27) since the only number is negative (which we then don't
need). But with the current formula, it returns the date 18/02/06. The
two sets of data should not be mixed with each other and the
spreadsheet is massive so that I can't get rid of the upper part above
STOP to have it work.

I hope this helps, your help is much appreciated!!!

Gaetan

Bob Phillips wrote:

> What does your data look like,and where are you expecting to stop?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Thanks a lot for your answer Bob, but it still doesn't work and keeps
> > taking numbers above the row 20.
> > To be more accurate, as you can see I would like the code to start in a
> > cell (here B24) and look upside until cell B5, but not above this cell
> > (the code you gave me keeps going above), and from then go to the left
> > and copy the number to be pasted elsewhere.
> > So that if there is nothing (or a negative number) between rows B24 and
> > B5, it will return "" in B27, and not go for anything above.
> >
> > here is the whole code:
> >
> > Sub test5()
> >
> > Range("B24").Select
> >
> > Do
> > Selection.End(xlUp).Select
> > If Selection.Row < 5 Then Exit Do
> > Loop Until ActiveCell > 0
> >
> > Selection.End(xlToLeft).Select
> >
> > ActiveCell.Copy
> > Range("B27").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> >
> > End Sub
> >
> >
> > Thanks very much for your help!
> >
> > Gaetan
> >
> > Bob Phillips wrote:
> >
> > > Do
> > > Selection.End(xlUp).Select
> > > If Selection.Row < 20 Then Exit Do
> > > Loop Until ActiveCell > 0
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi,
> > > >
> > > > I am trying to look for the first positive number in a given colomn,
> > > > but I want the loop to stop looping at a certain row and stay in a
> > > > given range.
> > > >
> > > > Here is my current code:
> > > >
> > > > Do
> > > > Selection.End(xlUp).Select
> > > > Loop Until ActiveCell > 0
> > > >
> > > > How can I have the loop to stop looking up say at row 100?
> > > >
> > > > Many thanks,
> > > >
> > > > Gaetan
> > > >
> >

[Bob Phillips]

It seems toi me that all you want is

Range("B24").End(xlUp).Offset(-1, 0).End(xlToLeft).Copy Range("B27")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> 15/02/2006 12125
> 16/02/2006 211254
> 17/02/2006 14212
> 18/02/2006
>
> STOP
> 17/08/2006
> 18/08/2006
> 19/08/2006
> 20/08/2006
> 21/08/2006
> 22/08/2006
> 23/08/2006 -132113
> 24/08/2006
>
> There is in this example no number that should be returned in the range
> selected (B27) since the only number is negative (which we then don't
> need). But with the current formula, it returns the date 18/02/06. The
> two sets of data should not be mixed with each other and the
> spreadsheet is massive so that I can't get rid of the upper part above
> STOP to have it work.
>
> I hope this helps, your help is much appreciated!!!
>
> Gaetan
>
> Bob Phillips wrote:
>
> > What does your data look like,and where are you expecting to stop?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks a lot for your answer Bob, but it still doesn't work and keeps
> > > taking numbers above the row 20.
> > > To be more accurate, as you can see I would like the code to start in
a
> > > cell (here B24) and look upside until cell B5, but not above this cell
> > > (the code you gave me keeps going above), and from then go to the left
> > > and copy the number to be pasted elsewhere.
> > > So that if there is nothing (or a negative number) between rows B24
and
> > > B5, it will return "" in B27, and not go for anything above.
> > >
> > > here is the whole code:
> > >
> > > Sub test5()
> > >
> > > Range("B24").Select
> > >
> > > Do
> > > Selection.End(xlUp).Select
> > > If Selection.Row < 5 Then Exit Do
> > > Loop Until ActiveCell > 0
> > >
> > > Selection.End(xlToLeft).Select
> > >
> > > ActiveCell.Copy
> > > Range("B27").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > > Application.CutCopyMode = False
> > >
> > > End Sub
> > >
> > >
> > > Thanks very much for your help!
> > >
> > > Gaetan
> > >
> > > Bob Phillips wrote:
> > >
> > > > Do
> > > > Selection.End(xlUp).Select
> > > > If Selection.Row < 20 Then Exit Do
> > > > Loop Until ActiveCell > 0
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi,
> > > > >
> > > > > I am trying to look for the first positive number in a given
colomn,
> > > > > but I want the loop to stop looping at a certain row and stay in a
> > > > > given range.
> > > > >
> > > > > Here is my current code:
> > > > >
> > > > > Do
> > > > > Selection.End(xlUp).Select
> > > > > Loop Until ActiveCell > 0
> > > > >
> > > > > How can I have the loop to stop looking up say at row 100?
> > > > >
> > > > > Many thanks,
> > > > >
> > > > > Gaetan
> > > > >
> > >
>