Results 1 to 7 of 7
Thread: LOOP (Excel 2003)

20060131, 15:03 #1
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
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

20060131, 15:14 #2
 Join Date
 Apr 2003
 Location
 Hampshire, United Kingdom
 Posts
 602
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 SubWaggers
If at first you do succeed, you've probably missed something.

20060131, 20:01 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,313
 Thanks
 3
 Thanked 211 Times in 194 Posts
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?Regards,
Rory
Microsoft MVP  Excel

20060201, 10:23 #4
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
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

20060201, 10:27 #5
 Join Date
 Apr 2003
 Location
 Hampshire, United Kingdom
 Posts
 602
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: LOOP (Excel 2003)
Mod divides one number by another, and returns only the remainder. See Mod Operator
Waggers
If at first you do succeed, you've probably missed something.

20060201, 10:35 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: LOOP (Excel 2003)
If you click in the word Mod in the code and press F1, you'll get help on this function.

20060201, 11:06 #7
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: LOOP (Excel 2003)
Thanks for the great tip!
The Mod Operator is a great tip!
Thanks Darryl.