Why wouldn't this statement bail out after 150 rows?

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row Step 1

I use a 'If RowNdx 150 then exit sub' in my loop to bail out when needed,
but I'm sure there's a better way.

Thanks for the help.

RJH

Hi RJD,

Why you don't simply use For RowNdx = 1 to 150?
I think we need to know the whole code inside of the For Next looping.


--
Regards,
Colo
http://www.puremis.net/excel/


"RJH" wrote in message
...
Why wouldn't this statement bail out after 150 rows?

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row Step
1

I use a 'If RowNdx 150 then exit sub' in my loop to bail out when
needed,
but I'm sure there's a better way.

Thanks for the help.

RJH

Looks fine to me...

This outputs 1 to 150 as expected.

Sub test()
Dim RowNdx As Long

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row
Step 1
Debug.Print RowNdx
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"RJH" wrote in message
...
Why wouldn't this statement bail out after 150 rows?

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row Step
1

I use a 'If RowNdx 150 then exit sub' in my loop to bail out when
needed, but I'm sure there's a better way.

Thanks for the help.

RJH

If you have to specify the start and end rows as arguments to the Cells
property, then it would seem to me that you might just as well say

For RowNdx = 1 To 150

there is no more dynamic aspect to your code.

Which leads on to, post the rest of the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RJH" wrote in message
...
Why wouldn't this statement bail out after 150 rows?

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row Step
1

I use a 'If RowNdx 150 then exit sub' in my loop to bail out when
needed,
but I'm sure there's a better way.

Thanks for the help.

RJH

And one more possibility

Dim rng as Range, cell as Range
Set rng = Sheets(1).Range(Sheets(1).Cells(1, 3), _
Sheets(1).Cells(150, 3))

for each cell in rng
msgbox cell.Address

Next


or

Dim rng as Range, cell as Range
set rng = Sheets(1).Cells(1,3).Resize(150,1)
for each cell in rng
msgbox cell.Address
Next

--
Regards,
Tom Ogilvy


"RJH" wrote in message
...
Why wouldn't this statement bail out after 150 rows?

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row Step
1

I use a 'If RowNdx 150 then exit sub' in my loop to bail out when
needed,
but I'm sure there's a better way.

Thanks for the help.

RJH

Your comments made me look closely at what I was trying to do.
I have a Do-Loop nested in the For-Next and the Do-Loop was still involved
at row 150.
That's why it wouldn't bail out.

Thanks for your help.

RJH



"RJH" wrote in message
...
Why wouldn't this statement bail out after 150 rows?

For RowNdx = Sheets(1).Cells(1, 3).Row To Sheets(1).Cells(150, 3).Row Step
1

I use a 'If RowNdx 150 then exit sub' in my loop to bail out when
needed, but I'm sure there's a better way.

Thanks for the help.

RJH