How to make a For/Next loop go from last value to first value

[jc352]

I am trying to use a For/Next loop to change the background color and add text to comboboxes numbered 1 thru 7. They are indicating a persons days off, so there are circumstances where boxes 7 and 1 will need to be used. I have both 8 and 10 hour shifts so there are also circumstances where 6, 7 and 1 are used as well as 7, 1 and 2. Is there a way to do this with a loop? Below is what I have so far.

[FONT=Calibri][SIZE=3][COLOR=#000000]For I = 1 To 7<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftstart" & I).BackColor =RGB(255, 255, 255)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftend" & I).BackColor =RGB(255, 255, 255)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftstart" & I).Value =""<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftend" & I).Value =""<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next I<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Sun/Mon/Tue" Then restdays = 1<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Mon/Tue/Wed" Then restdays = 2<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Tue/Wed/Thur" Then restdays = 3<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Wed/Thur/Fri" Then restdays = 4<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Thur/Fri/Sat" Then restdays = 5<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Fri/Sat/Sun" Then restdays = 6<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Sat/Sun/Mon" Then restdays = 7<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Sun/Mon" Then restdays = 8<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Mon/Tue" Then restdays = 9<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Tue/Wed" Then restdays = 10<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Wed/Thur" Then restdays = 11<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Thur/Fri" Then restdays = 12<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Fri/Sat" Then restdays = 13<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdaysset = "Sat/Sun" Then restdays = 14<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]If restdays < 6 Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest2 = restdays + 2<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest1 = restdays<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ElseIf restdays > 7 And restdays < 14 Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest1 = restdays - 7<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest2 = (restdays - 7) + 1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ElseIf restdays = 6 Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest1 = 6<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest2 = 7<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ElseIf restdays = 7 Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest1 = 1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest2 = 2<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ElseIf restdays = 14 Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest1 = 7<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rest2 = 1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For a = rest1 To rest2<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftstart" & a).BackColor =RGB(255, 255, 0)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftend" & a).BackColor =RGB(255, 255, 0)<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftstart" & a).Value ="REST"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Me.Controls("shiftend" & a).Value ="REST"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next a<o:p></o:p>[/COLOR][/SIZE][/FONT]

 

[sheetspread]

step -1

 

[Scott Huish]

If you just want to go through the loop backward:

For a=rest2 To rest1 Step -1

 

[jc352]

I would like it to move forward, then go back to the beginning when it hits 7. In this case, combobox 7 represents Saturday and combobox 1 represents Sunday. If someone has Saturday and Sunday off, how would I make that loop go from 7 to 1, yet still work for the rest of the week?

 

[jc352]

I guess this is something than can not be done.

 

[sheetspread]

If you're interested in replacing lines 7-20 with a formula:

restdays = ((2 + InStr("SunMonTueWedThuFriSat", Left(restdaysset, 3))) / 3) + Choose(Len(restdaysset) - Len(Replace(restdaysset, "/", "")), 7, 0)

I'm not sure what you're trying to do with the loop though

 

[Scott Huish]

I don't quite understand what your code is doing either.

However, if you need a loop to go through days of the week you need to extend the range of the loop past 7.
Is something like this what you need?

Sub test()
For m = 7 To 12
MsgBox ((m - 1) Mod 7) + 1
Next
End Sub

This will return 7,1,2,3,4,5