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

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

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

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

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

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