How do I reset a for next loop in the script?

Hello friends,

I have a nested for-next loop inside another for-next loop, but when the nested loop ends it does not reset to the beginning again for the next item in my array loop. I am pretty sure the problem lies in my declarartions and how VBA handles arrays used in For-Next loops. I am having a mental block concerning declaring arrays and their ranges. An example of the setup is below;

Dim simspg as variant
Dim simspoint as variant
simspg = Array("SP001", "SP002", "SP003", "SP004", "SP005", "SP006", "SP007", "SP008", "SP009", "SP010", "SP011", "SP012", "SP013", _
                       "SP014", "SP015", "SP016", "SP017", "SP018", "SP019", "SP020", "SP021")
For each simspoint in simspg 
For x = 1 to 100
Worksheets (simspoint).select
cells (x,1).value = 12
next
next

How would I properly use these two for-next loops such that "x" is reset to 1 each time a new member of the array is called?

Thanks for any help, Newbee BAT:-)

Dim simspg as variant
Dim simspoint as variant
simspg = Array("SP001", "SP002", "SP003", "SP004", "SP005", "SP006", "SP007", "SP008", "SP009", "SP010", "SP011", "SP012", "SP013", "SP014", "SP015", "SP016", "SP017", "SP018", "SP019", "SP020", "SP021")
For each simspoint in simspg 
  Worksheets (simspoint).select
  For x = 1 to 100
  cells (x,1).value = 12
  next
next

I'm sure you have a reason for looping through 100, but if you don't, you can simplify your code and resolve the issue by just avoiding it...

Sub Looper()
Dim simspg As Variant
Dim simspoint As Variant
simspg = Array("SP001", "SP002", "SP003", "SP004", "SP005", "SP006", "SP007", "SP008", "SP009", "SP010", "SP011", "SP012", "SP013", _
    "SP014", "SP015", "SP016", "SP017", "SP018", "SP019", "SP020", "SP021")
For Each simspoint In simspg
    Worksheets(simspoint).Select
    Range("A1:A100").Value = 12
Next
End Sub

But, if you MUST loop to 100, then try this:

Sub Looper2()
Dim simspg As Variant
Dim simspoint As Variant
simspg = Array("SP001", "SP002", "SP003", "SP004", "SP005", "SP006", "SP007", "SP008", "SP009", "SP010", "SP011", "SP012", "SP013", _
    "SP014", "SP015", "SP016", "SP017", "SP018", "SP019", "SP020", "SP021")
For Each simspoint In simspg
    x = 1
    Worksheets(simspoint).Select
    For x = 1 To 100
        Cells(x, 1).Value = 12
    Next
Next
End Sub

Originally Posted by BrianATrease

I didn't explain due to format control of post window, but the lines are staggered based upon hierarchy of script in the sheets I am working with. The staggering did not help there either. "x" still goes to 100 and does not reset. Thanks though, BAT:-)

If you had used code tags, your formatting would have remained

BrianATrease,

Welcome to the Excel Forum.

You do not have to use Select, or loop thru rows 1 to 100, in your macro.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
' stanleydgromjr, 10/15/2012
' http://www.excelforum.com/excel-programming-vba-macros/868458-how-doo-i-reset-a-for-next-loop-in-the-script.html
Dim simspg As Variant
Dim i As Long
Application.ScreenUpdating = False
simspg = Array("SP001", "SP002", "SP003", "SP004", "SP005", "SP006", "SP007", "SP008", "SP009", "SP010", "SP011", "SP012", "SP013", _
  "SP014", "SP015", "SP016", "SP017", "SP018", "SP019", "SP020", "SP021")
For i = LBound(simspg) To UBound(simspg)
  Worksheets(simspg(i)).Cells(1, 1).Resize(100).Value = 12
Next i
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Test macro.