Autofilling sequential numbers counting from inputted number in a userform

[bostenson]

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.

[p45cal]

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

[snb]

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

[bostenson]

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.