for next loops in visual basic excel

[chrislooney0]

Hi,

I wonder if anyone can explain the following. When I have a very
simple for next loop, for example:

for i=1 to worksheets.count
sheets(i).select
activesheet.name="a" & i
next i

the first time the macro goes through the loop, when you would expect
the value of i to be 1, the value of i is the highest value, i.e.
worksheets.count.

By the way, it does not seem to be consistent. If I stop and restart
the macro, it seems to go through the loop correctly i.e with i
starting from 1.

Can anybody explain how this works to me?

Thanks!

 

[paul.robinson]

Hi
When the loop runs, i will always start at 1. I suspect that you have
stopped the macro or a bug has occurred after the loop has run. If you
then look at the value of i it will show you its last executed value,
which at that point will be worksheets.count.
regards
Paul

 

[Mike]

Try this. You dont have to selet the sheet to rename it.
Sub reNameSheets()
Dim ws As Worksheet
Dim i As Long

For Each ws In Worksheets
i = i + 1
ws.Name = "a" & i
Next ws
End Sub

 

[Mike]

Or use your own code like this
Sub reName()
For i = 1 To Worksheets.Count
Sheets(i).Name = "a" & i
Next i
End Sub