| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range going up the column instead of down
Does anyone know how to set a range that will travel up the column from
the active cell until it hits a cell you specify? God bless jsd219 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range going up the column instead of down
do while activecell.row 3
activecell.offset(-1,0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message ps.com... Does anyone know how to set a range that will travel up the column from the active cell until it hits a cell you specify? God bless jsd219 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range going up the column instead of down
how do i tell it to stop when it has found a cell with a specific
value? i.e. For Each cell In rng Do While ActiveCell.Row 3 ActiveCell.Offset(-1, 0).Select Loop If cell.Value = UCase(cell.Value) Then cell.Select MsgBox "found" Exit Sub End If God bless jsd219 PS. i really am trying. :-) Tom Ogilvy wrote: do while activecell.row 3 activecell.offset(-1,0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message ps.com... Does anyone know how to set a range that will travel up the column from the active cell until it hits a cell you specify? God bless jsd219 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range going up the column instead of down
Change the line that has "Loop" in it to say:
Loop Until ActiveCell.Value=Your value. HTH Otto "jsd219" wrote in message oups.com... how do i tell it to stop when it has found a cell with a specific value? i.e. For Each cell In rng Do While ActiveCell.Row 3 ActiveCell.Offset(-1, 0).Select Loop If cell.Value = UCase(cell.Value) Then cell.Select MsgBox "found" Exit Sub End If God bless jsd219 PS. i really am trying. :-) Tom Ogilvy wrote: do while activecell.row 3 activecell.offset(-1,0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message ps.com... Does anyone know how to set a range that will travel up the column from the active cell until it hits a cell you specify? God bless jsd219 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range going up the column instead of down
Dim i as Long, cell as Range
Dim rng as Range for i = rng.count to 1 step -1 set cell = rng(i) if cell.Value = UCase(cell.Value) then cell.Select MsgBox "found" exit sub end if Next i Might work based on what you showed - it doesn't use the activecell, which is where you said you wanted to start. for that, you could use the code I gave modified for the condition you show Do While Ucase(ActiveCell.Value) < ActiveCell.Value ActiveCell.Offset(-1, 0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message oups.com... how do i tell it to stop when it has found a cell with a specific value? i.e. For Each cell In rng Do While ActiveCell.Row 3 ActiveCell.Offset(-1, 0).Select Loop If cell.Value = UCase(cell.Value) Then cell.Select MsgBox "found" Exit Sub End If God bless jsd219 PS. i really am trying. :-) Tom Ogilvy wrote: do while activecell.row 3 activecell.offset(-1,0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message ps.com... Does anyone know how to set a range that will travel up the column from the active cell until it hits a cell you specify? God bless jsd219 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range going up the column instead of down
Thank you everyone :-)
God bless jsd219 Tom Ogilvy wrote: Dim i as Long, cell as Range Dim rng as Range for i = rng.count to 1 step -1 set cell = rng(i) if cell.Value = UCase(cell.Value) then cell.Select MsgBox "found" exit sub end if Next i Might work based on what you showed - it doesn't use the activecell, which is where you said you wanted to start. for that, you could use the code I gave modified for the condition you show Do While Ucase(ActiveCell.Value) < ActiveCell.Value ActiveCell.Offset(-1, 0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message oups.com... how do i tell it to stop when it has found a cell with a specific value? i.e. For Each cell In rng Do While ActiveCell.Row 3 ActiveCell.Offset(-1, 0).Select Loop If cell.Value = UCase(cell.Value) Then cell.Select MsgBox "found" Exit Sub End If God bless jsd219 PS. i really am trying. :-) Tom Ogilvy wrote: do while activecell.row 3 activecell.offset(-1,0).Select Loop -- Regards, Tom Ogilvy "jsd219" wrote in message ps.com... Does anyone know how to set a range that will travel up the column from the active cell until it hits a cell you specify? God bless jsd219 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
| CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions | |||
| Transfering VBA Array Column Range to Excel Column Range | Excel Programming | |||
Hybrid Mode
