| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop to add +1 in cell? | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| loop through a cell. | Excel Programming | |||
| Loop using cell names | Excel Programming | |||
| get for loop to go on after blank cell? | Excel Programming | |||
Hybrid Mode
