VBA Loop until certain cell!

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

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
>

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
> >

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
> > >
>

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
> > > >
> >

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
> > > > >
> > >
>

Thanks for your answer, but it doesn't cease the search above the STOP,
where data can still be looked for. I'm looking for a code to only
"End(xlUp)" in a given range...


Bob Phillips wrote:

> 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
> > > > > >
> > > >
> >

I obviously don't get what you are trying to do. I thought you wanted to
work up from row 24 until you find a cell in column B that is blank? Where
am I wrong?

--
HTH

Bob Phillips

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

<[email protected]> wrote in message
news:[email protected]...
> Thanks for your answer, but it doesn't cease the search above the STOP,
> where data can still be looked for. I'm looking for a code to only
> "End(xlUp)" in a given range...
>
>
> Bob Phillips wrote:
>
> > 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
> > > > > > >
> > > > >
> > >
>

I will try to be more clear...
The code I am trying to build tries to get the first and the second
positive numbers in the column B (from one row to a another row, this
is the thing I don't manage to do) and to return their values with the
corresponding date on the left.
The problem I encounter is that if there is only one positive number in
the given range of the column, it will return also the positive number
it finds above the row that I want it to look at.

I hope I made myself understood... thanks!

Bob Phillips wrote:

> I obviously don't get what you are trying to do. I thought you wanted to
> work up from row 24 until you find a cell in column B that is blank? Where
> am I wrong?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for your answer, but it doesn't cease the search above the STOP,
> > where data can still be looked for. I'm looking for a code to only
> > "End(xlUp)" in a given range...
> >
> >
> > Bob Phillips wrote:
> >
> > > 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
> > > > > > > >
> > > > > >
> > > >
> >

Perhaps this will get you started

Sub Test()
Const FirstRow As Long = 5
Const LastRow As Long = 24
Dim fSecond As Boolean
Dim i As Long

For i = FirstRow To LastRow
If Cells(i, "B").Value > 0 Then
MsgBox Cells(i, "A").Value
If fSecond Then
Exit For
Else
fSecond = True
End If
End If
Next i

End Sub


--
HTH

Bob Phillips

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

<[email protected]> wrote in message
news:[email protected]...
> I will try to be more clear...
> The code I am trying to build tries to get the first and the second
> positive numbers in the column B (from one row to a another row, this
> is the thing I don't manage to do) and to return their values with the
> corresponding date on the left.
> The problem I encounter is that if there is only one positive number in
> the given range of the column, it will return also the positive number
> it finds above the row that I want it to look at.
>
> I hope I made myself understood... thanks!
>
> Bob Phillips wrote:
>
> > I obviously don't get what you are trying to do. I thought you wanted to
> > work up from row 24 until you find a cell in column B that is blank?
Where
> > am I wrong?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks for your answer, but it doesn't cease the search above the
STOP,
> > > where data can still be looked for. I'm looking for a code to only
> > > "End(xlUp)" in a given range...
> > >
> > >
> > > Bob Phillips wrote:
> > >
> > > > 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
> > > > > > > > >
> > > > > > >
> > > > >
> > >
>