1. ## LOOP (Excel 2003)

Hi all,
I'm trying to do a loop a certain number of times , have two variable count along with the main counter, how would I count to a certain number within a loop, once reaches a certain value in this case iweek to reset itself to 1once it reaches 13, and Isweek to 1 once it reaches 2. What I have know is not working.

Thanks Darryl.

Private Sub CommandButton1_Click()
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = Int((DTPicker1.Value - xDate) / 7)
iWeek = iWeek + 1
Isweek = Isweek + 1
For i = i + 1 To z
If iWeek = 14 Then
iWeek = 0
End If
If Isweek = 2 Then
Isweek = 0
End If
Next i
End Sub

2. ## Re: LOOP (Excel 2003)

On the whole you have the right idea, but things aren't quite in the right order (or using the right syntax). Does this do what you're after?...

Private Sub CommandButton1_Click()
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = Int((DTPicker1.Value - xDate) / 7)
For i = i + 1 To z
iWeek = iWeek + 1
Isweek = Isweek + 1
If iWeek = 14 Then
iWeek = 0
End If
If Isweek = 2 Then
Isweek = 0
End If
Next i
End Sub

3. ## Re: LOOP (Excel 2003)

I'm not entirely sure what you are after - if you reset Isweek to 1 once it reaches 2, it will always be 1. If you want the two variables to run 1,2,3.....13,1,2,3... and 1,2,1,2,1,2... then something like this should work:
<pre> Dim i As Integer
Dim iWeek As Integer
Dim Isweek As Integer
Dim z As Integer
z = Int((DTPicker1.Value - xDate) / 7)
For i = 1 To z
iWeek = iWeek Mod 13 + 1
Isweek = Isweek Mod 2 + 1
Debug.Print i; iWeek; Isweek
Next i
</pre>

If that's not what you are after, can you elaborate?

4. ## Re: LOOP (Excel 2003)

Hi Rory,

I thought that I closed this off, but didn't, I figured out that part out about the 2, thanks for replying to my post. You guys in here are best, and I wouldn't be able learn this stuff if it weren't for the many in her that take the time to help.

question, what is Mod for?

Thanks Darryl

5. ## Re: LOOP (Excel 2003)

Mod divides one number by another, and returns only the remainder. See Mod Operator

6. ## Re: LOOP (Excel 2003)

If you click in the word Mod in the code and press F1, you'll get help on this function.

7. ## Re: LOOP (Excel 2003)

Thanks for the great tip!

The Mod Operator is a great tip!

Thanks Darryl.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•