Results 1 to 8 of 8
  1. #1
    enayat77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7

    Loop through each portion/share of a division operation.

    Hello Everyone,
    It is my very first time posting in a Forum. I am a beginner to Access and VBA. I have a question and it is a bit difficult for me to explain it clearly in English. I hope you can understand what I am looking for.
    I want to paste the code below and explain what result I want.

    Public Function test()
    Dim a, b, c, counter As Long
    a = 9
    b = 3
    c = a / b
    counter = 1
    For i = 1 To b
    For j = 1 To c
    Debug.Print "Group#(" & counter & ") " & j
    Next
    Debug.Print "-----------"
    counter = counter + 1
    Next
    End Function

    Result:


    debug.print test
    Group#(1) 1
    Group#(1) 2
    Group#(1) 3
    -----------
    Group#(2) 1
    Group#(2) 2
    Group#(2) 3
    -----------
    Group#(3) 1
    Group#(3) 2
    Group#(3) 3
    -----------
    There are three groups. Group#1 started from 1 to 3, now for group#2 it want to display from 4 to 6 and for group#3 from 7 to 9.
    I hope it makes sense. Thank you so much for your help.
    Last edited by enayat77; 11-18-2021 at 04:19 PM. Reason: Rephrasing for clear understanding.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Please use code tags (highlight your code and click the # button) to preserve indentation

    As you had it written, only counter will have been typed as long, a, b and c would be typed as variant
    Also include Option Explicit at the top of each module as your variables i and j has not been typed so again would be typed as a variant

    see this revised code

    Code:
    Public Function test()
    Dim a As Long
    Dim b As Long
    Dim c As Long
    
    Dim counter As Long
    Dim i As Long
    Dim j As Long
    
        a = 9
        b = 3
        c = a / b
        
        For i = 1 To b
    
            For j = 1 To c
    
                counter = 1
                Debug.Print "Group#(" & counter & ") " & j
                counter = counter + 1
                
            Next
    
            Debug.Print "-----------"
    
        Next
    
    End Function

  3. #3
    enayat77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7
    Thank you so much for the quick response and useful guidance. However, the code you sent doesn't produce what I am looking for.
    I want the result of the division as a sequential number in sequential separate groups.
    When 9 is divided by 3, in my case it means three groups. The first group's value starts from 1, the second group's value should follow the last value of the the first group which should be 4.
    And the 3rd group should follow the same as 2nd group and should start from 7.

    Now it gives this result:
    Group#(1) 1
    Group#(1) 2
    Group#(1) 3
    -----------
    Group#(2) 1
    Group#(2) 2
    Group#(2) 3
    -----------
    Group#(3) 1
    Group#(3) 2
    Group#(3) 3
    -----------
    But I want as shown below.

    Group#(1) 1
    Group#(1) 2
    Group#(1) 3
    -----------
    Group#(2) 4
    Group#(2) 5
    Group#(2) 6
    -----------
    Group#(3) 7
    Group#(3) 8
    Group#(3) 9
    -----------
    It is really difficult for me to explain it in a better way. That is why I am trying to point out directly to the result I am looking for.
    And I really don't have any idea how the # button works for preserving indentation. As I said before it is my first time joining and posting in a forum.
    I still need to learn the rules in this Forum. Accept my apology for any mistake.
    I appreciate your assistance and patience.
    Last edited by enayat77; 11-18-2021 at 06:10 PM. Reason: Correction

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in that case

    Code:
    Public Function test()
    Dim a As Long
    Dim b As Long
    Dim c As Long
    
    Dim counter As Long
    Dim i As Integer
    Dim j As Integer
    
        a = 9
        b = 3
        c = a / b
        
        counter = 1
        For i = 1 To b
            
            For j = 1 To c
                
                Debug.Print "Group#(" & i & ") " & counter
                counter = counter + 1
                
            Next
    
            Debug.Print "-----------"
            
        Next
    
    End Function

  5. #5
    enayat77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7
    Awesome ! It worked. Thank you so much.
    I am applying the above scenario in a small project. In case of need, I may come back with questions.
    Cheers!

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    better to start a new thread for new questions

    good luck with your project

  7. #7
    enayat77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7
    Hello, I am back.
    Now that you understood my scenario, could you please show me how to display only the first and last value of each group.
    Like:

    Group#1 1 - 3
    Group#2 4 - 6
    Group#3 7 - 9
    So that I will insert them into a table like:


    Code:
    GroupID StartValue  EndValue
    1           1          3
    2           4          6
    3           7          9
    For indention, I used the # but I don't know if I used it properly.
    Thanks.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, I should have asked you what is the purpose of this exercise.

    Basically you have started from the wrong place. It would all be done with a query, you would not use vba - at least I don't see it at the moment

    It's late here and I'm just signing off. As I suggested before, start a new thread - and explain what you are trying to do, provide some background so responders have some context

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Division by zero error
    By MTSPEER in forum Queries
    Replies: 3
    Last Post: 10-20-2017, 12:14 PM
  2. Replies: 7
    Last Post: 01-21-2016, 08:59 PM
  3. division on vba with formating
    By joshynaresh in forum Forms
    Replies: 5
    Last Post: 10-23-2013, 06:04 AM
  4. #Num! error division by 0
    By smc678 in forum Access
    Replies: 5
    Last Post: 10-14-2013, 11:41 AM
  5. division by 0
    By kwooten in forum Queries
    Replies: 5
    Last Post: 03-19-2012, 04:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums