Error Within Nested For Loop

amuza · Jun 6th 2007

So I'm writing this code which determines the number based on values of a, b, and c. I figured a nested for loop was the best approach where there are 10 possible selections for a, 2 for b and 3 for c giving a total number of 60 combinations. When I try to run this though it gives me this:"Complie error: next without for" and highlights the first Next on line 14. From what I can tell, that Next does have a For connected to it but maybe I'm missing something. Any advice or corrections would be appreciated.

Below is the code, but on here the for's and next's don't line up as they do in my actual code in VBA.

Code

'Determines what number corresponds to the user's selections'
Sub numberdetermine()
Dim number As Integer, a As Integer, b As Integer, c As Integer
    a = Range("AB36").Value
    b = Range("AB37").Value
    c = Range("AB38").Value
    number = 0
    For i = 1 To 10
        For j = 1 To 2
            For k = 1 To 3
                number = number + 1
                If i = a And j = b And k = c Then
            Exit For
            Next k
        Next j
    Next i
Range("H8").Value = number
Range("H20").Value = number + 1
Range("H32").Value = number + 2
End Sub

Display More

WinteE · Jun 6th 2007

Re: Error Within Nested For Loop

Try this :

Code

Sub numberdetermine() 
    Dim number As Integer, a As Integer, b As Integer, c As Integer 
    a =  Range("AB36").Value 
    b = Range("AB37").Value 
    c = Range("AB38").Value 
    number = 0 
    For i = 1 To a 
        For j = 1 To b 
            For k = 1 To c 
                number = number + 1 
                Next k 
            Next j 
        Next i 
        Range("H8").Value = number 
        Range("H20").Value = number + 1 
        Range("H32").Value = number + 2 
    End Sub

Display More

shg · Jun 6th 2007

Re: Error Within Nested For Loop

You have an open Block If. Change

Code

If i = a And j = b And k = c Then 
                    Exit For

to

Code

If i = a And j = b And k = c Then Exit For

shg · Jun 6th 2007

Re: Error Within Nested For Loop

if your original logic was correct, then this works:

Code

Sub numberdetermine()
    Dim number As Integer, a As Integer, b As Integer, c As Integer
    
    a = Range("AB36").Value
    b = Range("AB37").Value
    c = Range("AB38").Value
    
    number = a * b * c
    If number > 10 * 2 * 3 Then number = 10 * 2 * 3 ' your old loop counts
    Range("H8").Value = number
    Range("H20").Value = number + 1
    Range("H32").Value = number + 2
End Sub

Display More

It will not always give the same result as WinteE's suggestion.

amuza · Jun 6th 2007

Re: Error Within Nested For Loop

Thank you for the replies. Although the suggestions cleared up a couple of things, the main task at hand was still not solved. Still looking at the code from a previous post, I've attached an excel sheet which may clarify what I'm looking for a little better. Within the sheet, you can select using combo-boxes what you want for a, b and c. And as you can see from the B3:E63, there are 60 possible combinations of the variables. I would like for my code to dtake in these three selections(a, b, and c) and come up with what corresponding number is being looked at.

shg · Jun 6th 2007

Re: Error Within Nested For Loop

In that case, there's no need for VBA:

C2: desired value for a
D2: desired value for b
E2: desired value for c

B2, entered as a array formula (Cntl-Shift-Enter instead of Enter)

=INDEX(B4:B63,MATCH(C2 & "|" & D2 & "|" & E2, C4:C63 & "|" & D4:D63 & "|" & E4:E63, 0) )

amuza · Jun 6th 2007

Re: Error Within Nested For Loop

Thank-you very much shg, that will work.

Error Within Nested For Loop

Participate now!

Share