Excel VBA: Loop to Fill in Sequence Numbers in a Column












0














I am new to Excel VBA and I am currently working with a project that needs to autofill sequence numbers in Column A depending on the filled range in Column B.



With the code below that I found in ozgrid.com forum, it autofills column A from 1 up to the last used row in the adjacent cell which is in Column B. But on my case is that I have a userform and it has a command button that when clicked, it should just continue to generate unique Reference ID in Column A from the maximum number on the last filled cell in the Column A up to the last filled cell in the adjacent cell which is the Column B.



And the problem with the code below is that on the first time the command button is clicked, it generates sequence numbers from 1 up to the last used row in Column B, but when the command button is clicked again, Column A is autofilled from 1 to last used row again instead of continuing the max number that is existing in the Column A.



For example, the max number on the first click in the command button is 82. 
On the next click of the command button, the cell below the last used row should be 83 up to the last used row in the Column B again.



Also, I have provided a picture of my GUI below for your better reference.



Sub SequenceLoop()
Dim k, i As Long, n As Long
Dim LR1 As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

LR1 = ws.Range("B" & ws.Rows.Count).End(xlUp).Row


With Range("A2:A" & LR1)
k = .Value
For i = 1 To UBound(k, 1)
If Len(k(i, 1)) = 0 Then
n = n + 1
k(i, 1) = n
End If
Next
.Value = k
End With
End Sub


GUI for autofilling data in Column B. When Create Template Button is Clicked, it autofills the specific data in Column B:





The GUI for autofilling sequence numbers in Column A:



       










share|improve this question





























    0














    I am new to Excel VBA and I am currently working with a project that needs to autofill sequence numbers in Column A depending on the filled range in Column B.



    With the code below that I found in ozgrid.com forum, it autofills column A from 1 up to the last used row in the adjacent cell which is in Column B. But on my case is that I have a userform and it has a command button that when clicked, it should just continue to generate unique Reference ID in Column A from the maximum number on the last filled cell in the Column A up to the last filled cell in the adjacent cell which is the Column B.



    And the problem with the code below is that on the first time the command button is clicked, it generates sequence numbers from 1 up to the last used row in Column B, but when the command button is clicked again, Column A is autofilled from 1 to last used row again instead of continuing the max number that is existing in the Column A.



    For example, the max number on the first click in the command button is 82. 
    On the next click of the command button, the cell below the last used row should be 83 up to the last used row in the Column B again.



    Also, I have provided a picture of my GUI below for your better reference.



    Sub SequenceLoop()
    Dim k, i As Long, n As Long
    Dim LR1 As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    LR1 = ws.Range("B" & ws.Rows.Count).End(xlUp).Row


    With Range("A2:A" & LR1)
    k = .Value
    For i = 1 To UBound(k, 1)
    If Len(k(i, 1)) = 0 Then
    n = n + 1
    k(i, 1) = n
    End If
    Next
    .Value = k
    End With
    End Sub


    GUI for autofilling data in Column B. When Create Template Button is Clicked, it autofills the specific data in Column B:





    The GUI for autofilling sequence numbers in Column A:



           










    share|improve this question



























      0












      0








      0







      I am new to Excel VBA and I am currently working with a project that needs to autofill sequence numbers in Column A depending on the filled range in Column B.



      With the code below that I found in ozgrid.com forum, it autofills column A from 1 up to the last used row in the adjacent cell which is in Column B. But on my case is that I have a userform and it has a command button that when clicked, it should just continue to generate unique Reference ID in Column A from the maximum number on the last filled cell in the Column A up to the last filled cell in the adjacent cell which is the Column B.



      And the problem with the code below is that on the first time the command button is clicked, it generates sequence numbers from 1 up to the last used row in Column B, but when the command button is clicked again, Column A is autofilled from 1 to last used row again instead of continuing the max number that is existing in the Column A.



      For example, the max number on the first click in the command button is 82. 
      On the next click of the command button, the cell below the last used row should be 83 up to the last used row in the Column B again.



      Also, I have provided a picture of my GUI below for your better reference.



      Sub SequenceLoop()
      Dim k, i As Long, n As Long
      Dim LR1 As Long
      Dim ws As Worksheet

      Set ws = ThisWorkbook.Sheets("Sheet1")

      LR1 = ws.Range("B" & ws.Rows.Count).End(xlUp).Row


      With Range("A2:A" & LR1)
      k = .Value
      For i = 1 To UBound(k, 1)
      If Len(k(i, 1)) = 0 Then
      n = n + 1
      k(i, 1) = n
      End If
      Next
      .Value = k
      End With
      End Sub


      GUI for autofilling data in Column B. When Create Template Button is Clicked, it autofills the specific data in Column B:





      The GUI for autofilling sequence numbers in Column A:



             










      share|improve this question















      I am new to Excel VBA and I am currently working with a project that needs to autofill sequence numbers in Column A depending on the filled range in Column B.



      With the code below that I found in ozgrid.com forum, it autofills column A from 1 up to the last used row in the adjacent cell which is in Column B. But on my case is that I have a userform and it has a command button that when clicked, it should just continue to generate unique Reference ID in Column A from the maximum number on the last filled cell in the Column A up to the last filled cell in the adjacent cell which is the Column B.



      And the problem with the code below is that on the first time the command button is clicked, it generates sequence numbers from 1 up to the last used row in Column B, but when the command button is clicked again, Column A is autofilled from 1 to last used row again instead of continuing the max number that is existing in the Column A.



      For example, the max number on the first click in the command button is 82. 
      On the next click of the command button, the cell below the last used row should be 83 up to the last used row in the Column B again.



      Also, I have provided a picture of my GUI below for your better reference.



      Sub SequenceLoop()
      Dim k, i As Long, n As Long
      Dim LR1 As Long
      Dim ws As Worksheet

      Set ws = ThisWorkbook.Sheets("Sheet1")

      LR1 = ws.Range("B" & ws.Rows.Count).End(xlUp).Row


      With Range("A2:A" & LR1)
      k = .Value
      For i = 1 To UBound(k, 1)
      If Len(k(i, 1)) = 0 Then
      n = n + 1
      k(i, 1) = n
      End If
      Next
      .Value = k
      End With
      End Sub


      GUI for autofilling data in Column B. When Create Template Button is Clicked, it autofills the specific data in Column B:





      The GUI for autofilling sequence numbers in Column A:



             







      microsoft-excel vba






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jun 25 '16 at 21:50









      Scott

      15.5k113889




      15.5k113889










      asked Jun 23 '16 at 12:31









      John Sherwin Rulete

      112




      112






















          1 Answer
          1






          active

          oldest

          votes


















          0














          The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes.



          The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro.



          Something like the following would do the trick.



          n = Cells(Rows.Count, 1).End(xlUp).Value





          share|improve this answer





















            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "3"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1092680%2fexcel-vba-loop-to-fill-in-sequence-numbers-in-a-column%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes.



            The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro.



            Something like the following would do the trick.



            n = Cells(Rows.Count, 1).End(xlUp).Value





            share|improve this answer


























              0














              The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes.



              The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro.



              Something like the following would do the trick.



              n = Cells(Rows.Count, 1).End(xlUp).Value





              share|improve this answer
























                0












                0








                0






                The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes.



                The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro.



                Something like the following would do the trick.



                n = Cells(Rows.Count, 1).End(xlUp).Value





                share|improve this answer












                The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes.



                The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro.



                Something like the following would do the trick.



                n = Cells(Rows.Count, 1).End(xlUp).Value






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jun 23 '16 at 23:48









                Code39

                213




                213






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Super User!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.





                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                    Please pay close attention to the following guidance:


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1092680%2fexcel-vba-loop-to-fill-in-sequence-numbers-in-a-column%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Probability when a professor distributes a quiz and homework assignment to a class of n students.

                    Aardman Animations

                    Are they similar matrix