# 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

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!!