MrExcel Publishing
Your One Stop for Excel Tips & Solutions

nested For loops


Posted by Doug on June 06, 2001 1:42 PM

is it possible to have two for loops inside each other and have the second do a next, variable before the first...for example

Sheets("Sheet2").Select
For u = 1 To LastRow
i$ = u
Dim apvalue As String
apvalue$ = Range("AP" + i$).Value

firstdigit2$ = Left((apvalue), 1)
seconddigit2$ = Right((Range("AP" + i$).Value), 1)
If firstdigit2$ = "0" Then
If seconddigit2$ = i$ Then
GoSub collectvalues2
End If
Else
If Range("AP" + i$).Value = i$ Then
GoSub collectvalues2
End If
End If

Next u


Gosub insertdata
Next p

I get an invalid control reference variable on the "Next p" statement. Anyone know how i can fix this and still get the results i want. Thanks!


Posted by Barrie Davidson on June 06, 2001 1:46 PM

firstdigit2$ = Left((apvalue), 1) seconddigit2$ = Right((Range("AP" + i$).Value), 1) Next u Gosub insertdata

If I understand your question properly, the answer is yes. One question for you, where is your "For" statement for the variable "p"? I think that might be your problem.

Regards,
Barrie

Posted by doug on June 06, 2001 1:50 PM

firstdigit2$ = Left((apvalue), 1) seconddigit2$ = Right((Range("AP" + i$).Value), 1) Next u Gosub insertdata

oops tried to shorten code way too much..lol..here it is...

districts:
'for loop to find number of districts by finding the highest numbered district

For x = 2 To LastRow
l$ = x
If Range("BF" + l$).Value > numofdistricts Then
numofdistricts = Range("BF" + l$).Value
End If
Next x
'testing for 1 or more districts
For p = 1 To numofdistricts + 1
If numofdistricts >= p Then
'for loop to get values for district 1
For y = 2 To LastRow
m$ = y
activedistrict$ = p
firstdigit = Left((Range("BF" + m$).Value), 1)
seconddigit = Right((Range("BF" + m$).Value), 1)
If firstdigit = "0" Then
If seconddigit = activedistrict$ Then
GoSub collectvalues1
End If
Else
If Range("BF" + m$).Value = activedistrict$ Then
'collect values from cells
GoSub collectvalues1

End If
End If

'collect information from sheet2
Sheets("Sheet2").Select
For u = 1 To LastRow
i$ = u
Dim apvalue As String
apvalue$ = Range("AP" + i$).Value

firstdigit2$ = Left((apvalue), 1)
seconddigit2$ = Right((Range("AP" + i$).Value), 1)
If firstdigit2$ = "0" Then
If seconddigit2$ = i$ Then
GoSub collectvalues2
End If
Else
If Range("AP" + i$).Value = i$ Then
GoSub collectvalues2
End If
End If

Next u


GoSub insertdata
Next p
Return

Posted by Barrie Davidson on June 06, 2001 1:58 PM

firstdigit2$ = Left((apvalue), 1) seconddigit2$ = Right((Range("AP" + i$).Value), 1) Next u Gosub insertdata

Holy Smokes, talk about your complicated code....

I think the problem lies in the location of your statement "Next p". Try putting it after your "Return" statement.

Let me know if I got it right. If not, I'll try again.

Barrie :)

Posted by Doug on June 06, 2001 2:12 PM

firstdigit2$ = Left((apvalue), 1) seconddigit2$ = Right((Range("AP" + i$).Value), 1) Next u Gosub insertdata

After the return statement the program ends...now it is saying next without for. not sure why i didn't change anything. Any suggestions on how i can clean it up or something? I mean i need it to cycle through alot of cells alot of different times....thanks!

Posted by Barrie Davidson on June 06, 2001 2:45 PM

firstdigit2$ = Left((apvalue), 1) seconddigit2$ = Right((Range("AP" + i$).Value), 1) Next u Gosub insertdata

I think I found your problem. I added a few lines and changed some of the order.

districts:
'for loop to find number of districts by finding the highest numbered district

For x = 2 To LastRow
l$ = x
If Range("BF" + l$).Value > numofdistricts Then
numofdistricts = Range("BF" + l$).Value
End If
Next x
'testing for 1 or more districts
For p = 1 To numofdistricts + 1
If numofdistricts >= p Then
'for loop to get values for district 1
For y = 2 To LastRow
m$ = y
activedistrict$ = p
firstdigit = Left((Range("BF" + m$).Value), 1)
seconddigit = Right((Range("BF" + m$).Value), 1)
If firstdigit = "0" Then
If seconddigit = activedistrict$ Then
GoSub collectvalues1
End If
Else
If Range("BF" + m$).Value = activedistrict$ Then
'collect values from cells
GoSub collectvalues1

End If
End If

'collect information from sheet2
Sheets("Sheet2").Select
For u = 1 To LastRow
i$ = u
Dim apvalue As String
apvalue$ = Range("AP" + i$).Value

firstdigit2$ = Left((apvalue), 1)
seconddigit2$ = Right((Range("AP" + i$).Value), 1)
If firstdigit2$ = "0" Then
If seconddigit2$ = i$ Then
GoSub collectvalues2
End If
Else
If Range("AP" + i$).Value = i$ Then
GoSub collectvalues2
End If
End If
Next u
Next y
End If


GoSub insertdata
Next p
Return

I added another "End If" (before "Next p"). You have seven If's and six End If's. Also, I added "Next y" (also before "Next p") because you had a For statement for values of "y" but I didn't spot an end to that loop.

Again, let me know if I'm in the ballpark here.

Regards,
Barrie

Posted by Doug on June 08, 2001 6:15 AM

firstdigit2$ = Left((apvalue), 1) seconddigit2$ = Right((Range("AP" + i$).Value), 1) Next u Gosub insertdata


Don't get the error any more. I knew i would get lost in all those conditionals, but seemed the only way to get it done...Thanks for your time and your help!!