Fill in cells based off VBA User Form input

[osscie3]

Hi all,

I've created a User Form that loads up upon opening up my spreadsheet. My goal is to take that user input and have it be placed in cells appropriately.

Here's my form fields:

First/Last Name
Account Number
Payment Amount
Due Date
# of Coupons

The user inputs data into this form once.

This is where it gets tricky for me. Let's say the user enters:

First/Last Name : John Smith
Account Number: 1234
Payment Amount: $400.00
Due Date: 04/01/2019
# of Coupons: 5

I'd want the end result to look like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]First/Last Name[/TD]
[TD]Account Number[/TD]
[TD]Payment Amount[/TD]
[TD]Due Date[/TD]
[TD]Coupon ID[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]1234[/TD]
[TD]$400.00[/TD]
[TD]04/01/2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]1234[/TD]
[TD]$400.00[/TD]
[TD]05/01/2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]1234[/TD]
[TD]$400.00[/TD]
[TD]06/01/2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]1234[/TD]
[TD]$400.00[/TD]
[TD]07/01/2019[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]1234[/TD]
[TD]$400.00[/TD]
[TD]08/01/2019[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Notice that name, account and payment are all the same. I need due date to increment by 1 month per row as well as the coupon ID to increment by 1 per row as well.

Here's my code so far. Disregard Trailer variable.

Dim fullName As LongDim AccountNumber As Long
Dim Trailer As Long
Dim PaymentAmount As Long
Dim DueDate As Long
Dim DataSheet As Worksheet
Dim DataSheetLasRow As Long
With DataSheet
    DataSheetLastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
Dim CurrentRow As Long




fullName = firstNameTxt.Value
AccountNumber = accountNumberTxt.Value
Trailer = trailerTxt.Value
PaymentAmount = paymentAmountTxt.Value
DueDate = dueDateTxt.Value


Range("A1").Value = "Full Name"
Range("B1").Value = "Account Number"
Range("C1").Value = "Trailer"
Range("D1").Value = "Payment Amount"
Range("E1").Value = "Due Date"


For CurrentRow = 2 To DataSheetLastRow


    fullName = DataSheet.Cells(CurrentRow, "A").Value
    AccountNumber = DataSheet.Cells(CurrentRow, "B").Value
    Trailer = DataSheet.Cells(CurrentRow, "C").Value
    PaymentAmount = DataSheet.Cells(CurrentRow, "D").Value
    DueDate = DataSheet.Cells(CurrentRow, "E").Value
    
Next

 

[DanteAmor]

I guess you're going to have a "CouponTxt" textbox to put the number of Coupons

Try this:

Private Sub CommandButton1_Click()
    Dim lr As Long, i As Long
    
    lr = Range("A" & Rows.Count).End(xlUp).Row + 1
    For i = 1 To CouponTxt.Value
        Cells(lr, "A").Value = firstNameTxt.Value
        Cells(lr, "B").Value = accountNumberTxt.Value
        Cells(lr, "C").Value = paymentAmountTxt.Value
        Cells(lr, "D").Value = DateSerial(Year(dueDateTxt), Month(dueDateTxt) + i - 1, Day(dueDateTxt))
        Cells(lr, "E").Value = i
        lr = lr + 1
    Next
End Sub

 

[mikerickson]

No looping

Sub test()
    With Range("A:A")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            With Resize(Val(txtCouponCount.Text), 1)
                .Offset(0, 0).Value = txtFirstLastName.Text
                .Offset(0, 1).Value = txtAccountNumber.Text
                .Offset(0, 2).Value = txtPaymentAmount.Text
                .Offset(0, 3).Value = txtDueDate.Text
                .Offset(0, 4).Value = ["ROW()-" & (.Row-1)]
            End With
        End With
    End With
End Sub

 

[DanteAmor]

Hi mike:

No looping

Sub test()
    With Range("A:A")
        With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            With [SIZE=3][B][COLOR=#ff0000].[/COLOR][/B][/SIZE]Resize(Val(txtCouponCount.Text), 1)    '[COLOR=#ff0000]missing dot[/COLOR]
                .Offset(0, 0).Value = txtFirstLastName.Text
                .Offset(0, 1).Value = txtAccountNumber.Text
                .Offset(0, 2).Value = txtPaymentAmount.Text
                .Offset(0, 3).Value = txtDueDate.Text
                .Offset(0, 4).Value = ["ROW()-" & (.Row-1)]
            End With
        End With
    End With
End Sub

Missing increase the date one month each row

 

[DanteAmor]

without loop

Private Sub CommandButton1_Click()
    lr = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lr).Resize(CouponTxt, 5).Value = Array(firstNameTxt, accountNumberTxt, paymentAmountTxt, CDate(dueDateTxt), 1)
    Range("A" & lr).Offset(0, 3).Resize(CouponTxt).DataSeries xlColumns, xlChronological, xlMonth, 1, , False
    Range("A" & lr).Offset(0, 4).Resize(CouponTxt).DataSeries xlColumns, xlLinear, xlDay, 1, , False
End Sub

 

[mikerickson]

That's what I get for not testing code.

 

[DanteAmor]

Do not worry. I do not have as much confidence to deliver a code without trying, hehehe.

 

[osscie3]

This worked beautifully. Thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.