Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    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

  2. #2
    5 Star Lounger
    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 Sub
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  4. #4
    4 Star Lounger
    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

  5. #5
    5 Star Lounger
    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&#39;ve probably missed something.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    4 Star Lounger
    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.

Posting Permissions

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