'For - Next' question

[RJH]

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

 

[Colo]

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.

 

[Rob van Gelder]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[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