Fill in cells based off VBA User Form input

osscie3

Board Regular
Joined
Apr 30, 2014
Messages
70
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:

First/Last NameAccount NumberPayment AmountDue DateCoupon ID
John Smith1234$400.0004/01/20191
John Smith1234$400.0005/01/20192
John Smith1234$400.0006/01/20193
John Smith1234$400.0007/01/20194
John Smith1234$400.0008/01/20195

<tbody>
</tbody>

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.

Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I guess you're going to have a "CouponTxt" textbox to put the number of Coupons

Try this:

Code:
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
 
Upvote 0
No looping
Code:
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
 
Upvote 0
Hi mike:

No looping
Code:
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
 
Last edited:
Upvote 0
without loop

Code:
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
 
Last edited:
Upvote 0
Do not worry. I do not have as much confidence to deliver a code without trying, hehehe.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top