Error message: Invalid next control variable reference

Question

Trying to run the following loop but keep getting an invalid next control variable reference error. Cannot seem to work out the solution and I am sure it is a simple one.

The code works fine without the loop but when I have added the loop it has now failed.

Sub QC()

Dim i, j As Integer
Dim ws As String

For i = 1 To 2
For j = 0 To 1
ws = "Settings"

    Worksheets(ws).Range(Worksheets("QC").Cells(i * 5 - j, 2)) = Worksheets("QC").Cells(i * 5 - j, 3)
    Worksheets("QC").Cells(8, 5) = Worksheets("CE Results").Cells(9, 9)
    Worksheets(ws).Range(Worksheets("QC").Cells(i * 5 - j, 2)) = Worksheets("QC").Cells(i * 5, 2)

    Worksheets(ws).Range(Worksheets("QC").Cells(i * 5 - j, 2)) = Worksheets("QC").Cells(i * 5 + j, 3)
    Worksheets("QC").Cells(10, 5) = Worksheets("CE Results").Cells(9, 9)
    Worksheets(ws).Range(Worksheets("QC").Cells(i * 5 - j, 2)) = Worksheets("QC").Cells(i * 5, 2)

Next i
Next k

End Sub

Have fixed that but doesn't seem to solve the error, still getting "invalid next control variable reference". — tboo132, Oct 15 '19 at 19:40

score 0 · Answer 1 · answered Oct 16 '19 at 06:45

First of all, your loop variables are i and j, in Next statement you have k and i., Swith k to j.

Also, order of Nexts is wrong, becuase Next i corresponds to For j, so order of Nexts should be:

Next j
Next i

Also, you can omit variables in Next, which would be les error prone.

One more note: use proper indentation :)

Pᴇʜ · Answer 2 · 2019-10-16T06:52:18.553

If you open the i loop structure first you need to close it last. Always format your code correctly (use indentation as below) so you see that the loop structure you open last (here j) needs to be closed first.

You tried it the other way round so you got an error.

Dim i As Long, j As Long
For i = 1 To 2
    For j = 0 To 1
        ' your code
    Next j
Next i

Also note that if you declare Dim i, j As Integer that means j is Integer but i is Variant. You must specify a type for every variable Dim i As Long, j As Long otherwise VBA assumes Variant by default.

And you should declare your variables as Long because Excel has more rows than Integer can handle. Actually you can always use Long instead of Integer as there is no benefit in using Integer: Also see Why Use Integer Instead of Long?

Additional recommendation:

Don't declare Dim ws As String instead

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Settings")

and use it like:

ws.Range(…)

you can use this technique for your other worksheets too so your code gets a lot shorter and easier to maintain. Eg. if a name of a worksheet changes you have to change it only once in the Set statement.

Error message: Invalid next control variable reference

2 Answers2