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:
<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.
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 Name | Account Number | Payment Amount | Due Date | Coupon ID |
John Smith | 1234 | $400.00 | 04/01/2019 | 1 |
John Smith | 1234 | $400.00 | 05/01/2019 | 2 |
John Smith | 1234 | $400.00 | 06/01/2019 | 3 |
John Smith | 1234 | $400.00 | 07/01/2019 | 4 |
John Smith | 1234 | $400.00 | 08/01/2019 | 5 |
<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