for next loops in visual basic excel

  • Thread starter Thread starter chrislooney0
  • Start date Start date
C

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!
 
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
 
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
 
Or use your own code like this
Sub reName()
For i = 1 To Worksheets.Count
Sheets(i).Name = "a" & i
Next i
End Sub
 
Back
Top