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