How should I nest my 'For Next' statements?

Question

I have a number of 'For Next' statements, but I can't get them to run in the order I want them to. Basically four cells are filled, eight rows are added to the row number index (starting where the previous product was started) and then four cells are filled again:

Then we jump 1192 cells to the next machine and start the process again:

1. Machine 1 Component 0 Attribute 1
2. Machine 1 Component 0 Attribute 2

...

5. Machine 1 Component 1 Attribute 1
6. Machine 1 Component 1 Attribute 2

...

9. Machine 2 Component 0 Attribute 1
10. Machine 2 Component 0 Attribute 2

...

13. Machine 2 Component 1 Attribute 1

Here's how I believe the code should run. (I may be completely wrong about the structure here):

cycle through array of attributes K
    increment component number I
        increment S "jump 8 cells"
            increment finished product number R
                increment machine number M
                    reset finished product number R to 0
                            increment T "jump 1192 cells" 

                            increment T "jump 1192 cells" 
                    reset finished product number R to 0
                increment machine number M
            increment finished product number R 
        increment S "jump 8 cells"  
    increment component number I
cycle through array of attributes K

Here's my code:

Sub Button1_Click()
'r is the current finished product and starts at 0
r = 0

'k is attribute index. .length, .width, .height, .weight
Dim A(): ReDim A(3)
A(0) = "Length": A(1) = "Width": A(2) = "Height": A(3) = "Weight"
For K = LBound(A) To UBound(A)

    'i is part number. 0-19 for 20 components. limited to first part to make output smaller for testing.
    For i = 0 To 0

        's is row offset. 0 to 1184 for 149 finished products. adds 8 to the row index to start each finished product at the right row.
        For s = 0 To 8 Step 8

            'r is current finished product. increments by 1 here

            r = r + 1

                'm is machine index. 1,2,3,8. limited to 2 here to make output smaller for testing.
                Dim Q(): ReDim Q(1)
                Q(0) = "1": Q(1) = "2"
                For m = LBound(Q) To UBound(Q)

                    'p and t for machine row value offset. need to add 1192 for each machine. limited to 2 here to make output smaller for testing.
                    Dim P(): ReDim P(1)
                    P(0) = "0": P(1) = "1192"
                    For T = LBound(P) To UBound(P)

                        'this msgbox is helpful for debugging. it shows data stores in the variables for each cycle.
                        MsgBox "m" & Q(m) & " r" & r & " i" & i & " k" & A(K) & " Location: (" & 4 + s + K + P(T) & ", " & 2 + i & ")"
                        ProductName = "machine" & Q(m) & ".finishedProduct[" & r & "].component[" & i & "]." & A(K)


  If TypeName(Data) = "Error" Then
                            If MsgBox("Error reading machine" & Q(m) & ".finishedProduct[" & r & "].Component[" & i & "]. " & _
                            "Continue with read?", vbYesNo + vbExclamation, _
                            "Error") = vbNo Then Exit For
                            Else
                            'no error, put data in cell
                            End If


     '0=b4,1=c4,3=d4, etc.   FYI 2,4 = D2 so b4= 4,2 (+0 because first i value)
                        'writes result to cell. second iteration should start 8 rows below end of previous
                        Cells(4 + s + K + P(T), 2 + i) = ProductName

 'increment attribute name
Next K

  'increment component number
    Next i

     'increment row offset for finished product
        Next s

          'increment machine number
                Next m


            'increment machine row offset (1190 rows)
                    Next T

End Sub

I'm getting an invalid next control variable error at Next K as soon as the sub starts.

How can I make this work?

Answer 1

If I am reading this correctly:

Machines have multiple finished products Finished Products have multiple components Components have multiple attributes

Your innermost loop should handle the components. Next loop out should be finished products, next loop out should be Machines.

To troubleshoot, start new and: Get a for/next loop working to do components. Add a for/next loop outside of this (so the FOR is before it in the macro, the next is after it) to expand everything to multiple finished prodocuts Finally add a loop outside all of this for machines

Answer 2

You have to close the For Next in the inverse order you opened it.

For k = LBound(A) To UBound(A)
  ...
  For J = 1 To 9
    ...
    For m = 1 To 8 step 2
      ....
    Next m
    For n = 1 To 9 step 3
      ....
    Next n
  Next J
Next k