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

VBA Loop until certain cell!
 

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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ps.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ps.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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

VBA Loop until certain cell!
 

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)

wrote in message
ps.com...
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)

wrote in message
ps.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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)

wrote in message
ups.com...
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