Consulting

Results 1 to 4 of 4

Thread: Autofilling sequential numbers counting from inputted number in a userform

  1. #1
    VBAX Newbie
    Joined
    Mar 2020
    Location
    Ammon
    Posts
    2
    Location

    Autofilling sequential numbers counting from inputted number in a userform

    Hello all,

    I am trying to figure out how to autofill sequential numbers into a spreadsheet counting off from an inputted number in an excel userform. The userform is designed to write checks for certain employees, retirement account managers, and other payroll liabilities that I would not otherwise be able to track due to limitations with our payroll management company. The code for the userform is as follows:

    Private Sub cmdWriteChecks_Click()

    If Me.ListBox2.ListIndex < 0 Then
    MsgBox "Please select 'Payees'.", vbCritical
    Exit Sub
    End If

    If Me.txbCheckNumber.Value = "" Then
    MsgBox "Please enter 'Starting Check #'.", vbCritical
    Exit Sub
    End If

    Dim i As Integer
    Dim lr As Long
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("CheckRegister")

    Dim Selected_Payee As Integer
    Dim Write_Checks As Integer

    Selected_Payee = 0
    Write_Checks = 0

    For i = 0 To Me.ListBox2.ListCount - 1
    If Me.ListBox2.Selected(i) = True Then
    Selected_Payee = Selected_Payee + 1

    '''''' Check Duplicates ''''''

    If Application.WorksheetFunction.CountIfs(sh.Range("B:B"), Me.ListBox2.List(i, 0), sh.Range("C:C"), Me.txbDatePay.Value) = 0 Then
    lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

    '''''' Add to Check Register ''''''

    sh.Range("A" & lr + 1).Value = "=ROW()-1"
    sh.Range("B" & lr + 1).Value = Me.txbCheckNumber.Value
    sh.Range("C" & lr + 1).Value = Me.txbDatePay.Value
    sh.Range("D" & lr + 1).Value = Me.ListBox2.List(i, 2)
    sh.Range("E" & lr + 1).Value = Me.cmbPayPeriod.Value

    Write_Checks = 0
    End If
    End If
    Next i

    '''''' Clear Pay Details ''''''

    Me.txbCheckNumber.Value = ""
    Me.cmbPayPeriod.Value = ""

    End Sub

    The way this userform is to work, is that I have a list of payees to select from in ListBox2. Once I select the command button cmdWriteChecks a sheet known as CheckRegister is populated with the pay date in column C, payee name in column D, and the pay period in column E. From there, I will write in a procedure to open a new userform where I can populate the check amounts with each individual check number (but that's not part of my question, really). Where I'm hung up is how do I take the starting check number that I input in the userform, such as with the procedure sh.Range("B" & lr + 1).Value, and count out a sequence of check numbers based on the number of records I have selected and paste them into column B? I hope my question is specific enough, thank you in advance.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,901
    maybe, just maybe:
    Selected_Payee = 0
    Write_Checks = 0
    ThisCheckNo = Me.txbCheckNumber.Value '<<<<<<<<<<<<<<< add
    For i = 0 To Me.ListBox2.ListCount - 1
      If Me.ListBox2.Selected(i) = True Then
        Selected_Payee = Selected_Payee + 1
    
        '''''' Check Duplicates ''''''
    
        If Application.WorksheetFunction.CountIfs(sh.Range("B:B"), Me.ListBox2.List(i, 0), sh.Range("C:C"), Me.txbDatePay.Value) = 0 Then
          lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))
    
          '''''' Add to Check Register ''''''
    
          sh.Range("A" & lr + 1).Value = "=ROW()-1"
          sh.Range("B" & lr + 1).Value = ThisCheckNo '<<<<<<<<< change
          sh.Range("C" & lr + 1).Value = Me.txbDatePay.Value
          sh.Range("D" & lr + 1).Value = Me.ListBox2.List(i, 2)
          sh.Range("E" & lr + 1).Value = Me.cmbPayPeriod.Value
    
          Write_Checks = 0
          ThisCheckNo = ThisCheckNo + 1 '<<<<<<<<<< add
        End If
      End If
    
    Next i
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    Sub M_snb()
       redim sn(listbox1.listcount),1)
       y=val(inputbox("start at"))
    
       for j=0 to listbox1.listcount-1
         if listbox1.selected(j) then 
             sn(j,0)=listbox1.list(j,0) 
             sn(j,1)=y
             y=y+1
         end if
       next
    
       sheet1.cells(1,2).resize(ubound(sn),2)=sn
    End Sub

  4. #4
    VBAX Newbie
    Joined
    Mar 2020
    Location
    Ammon
    Posts
    2
    Location
    Thanks p45cal. That was just what I needed. Your two minute solution would've saved me just as many hours had I not asked before.
    Last edited by bostenson; 03-25-2020 at 08:01 AM. Reason: spelling

Tags for this Thread

Posting Permissions

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