Range going up the column instead of down

  • Thread starter Thread starter jsd219
  • Start date Start date
J

jsd219

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
 
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. :-)
 
Change the line that has "Loop" in it to say:
Loop Until ActiveCell.Value=Your value. HTH Otto
 
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
 
Thank you everyone :-)

God bless
jsd219


Tom said:
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
Click to expand...
 
Back
Top