Auto Insert Row With Predictive Data

[reberryjr]

I am in desperate need of some assistance. I'm trying to teach myself VBA in Excel 2010 and am building an application for a friend. I've come to a point where I'm at a standstill until I can figure this piece out. I've posted this question a couple of times and haven't received any responses, so I'm hoping that if I change the question around a little bit, someone will be willing to assist.

The application tracks several things for her business, where she currently has 30 Clients. The difficult piece for me is tracking payments. As of now, I have everyone on one tab, updated via UserForms. What I'd like to have happen is for the application to "predict" when the next payment is due, and auto-insert a row and copies the data from the most recent entry, into a new row, adjusting the next payment due date.

In other words, a new row would be created using the data in row 3 (red font). All of the information present would be copied into the new row, except the DP Next Due value would say 11/04/17. Another new row would be created using the data in row 6 (red font), except the DP Next Due value would say 1/26/18.

Thoughts?


[TABLE="class: grid, width: 989"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Today[/TD]
[TD]Updated[/TD]
[TD]Status[/TD]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Suffix[/TD]
[TD]Name[/TD]
[TD]DP Status[/TD]
[TD]DP Start[/TD]
[TD]DP Next Due[/TD]
[TD]DP Amt[/TD]
[TD]DP Pymt Date[/TD]
[TD]DP Paid[/TD]
[TD]DP Pymt Status[/TD]
[TD]DP Freq[/TD]
[/TR]
[TR]
[TD="align: right"]12/17/17[/TD]
[TD="align: right"]10/24/17[/TD]
[TD]Active[/TD]
[TD]Jennnifer[/TD]
[TD]Chu[/TD]
[TD][/TD]
[TD]Jennifer Chu[/TD]
[TD]Active[/TD]
[TD="align: right"]09/04/17[/TD]
[TD="align: right"]09/04/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]09/04/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD]Paid Timely[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: right"]12/17/17[/TD]
[TD="align: right"]10/24/17[/TD]
[TD]Active[/TD]
[TD]Jennnifer[/TD]
[TD]Chu[/TD]
[TD][/TD]
[TD]Jennifer Chu[/TD]
[TD]Active[/TD]
[TD="align: right"]09/04/17[/TD]
[TD="align: right"]09/04/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]10/04/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD]Paid Timely[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: right"]12/17/17[/TD]
[TD="align: right"]10/24/17[/TD]
[TD]Active[/TD]
[TD]Tom[/TD]
[TD]Smith[/TD]
[TD]Jr.[/TD]
[TD]Tom Smith Jr.[/TD]
[TD]Active[/TD]
[TD="align: right"]12/15/17[/TD]
[TD="align: right"]12/15/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]12/17/17[/TD]
[TD="align: right"]$200.00[/TD]
[TD]Paid Late[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]12/17/17[/TD]
[TD="align: right"]10/24/17[/TD]
[TD]Active[/TD]
[TD]Tom[/TD]
[TD]Smith[/TD]
[TD]Jr.[/TD]
[TD]Tom Smith Jr.[/TD]
[TD]Active[/TD]
[TD="align: right"]12/15/17[/TD]
[TD="align: right"]12/29/17[/TD]
[TD="align: right"]$250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Current[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]12/17/17[/TD]
[TD="align: right"]11/30/17[/TD]
[TD]Active[/TD]
[TD]Tom[/TD]
[TD]Smith[/TD]
[TD]Jr.[/TD]
[TD]Tom Smith Jr.[/TD]
[TD]Active[/TD]
[TD="align: right"]12/15/17[/TD]
[TD="align: right"]01/12/18[/TD]
[TD="align: right"]$250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Current[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

 

[The_Macrotect]

Hi there,

I'd like to help, but don't quite understand what you're looking for.

What would prompt excel automatically insert the new row? Does it need to happen after a certain date has passed?

What information would be used to "predict" when the next payment is due?

-Matt

 

[reberryjr]

Perhaps "predict" is the wrong term. It's really more of a calculation where "B" = Bi-Weekly, or 14 days, "M" = "Monthly" and "W" = "Weekly". I'm not certain how to best code the application to identify the need for the new row, with the next payment due. I wouldn't want it done only after a payment is made, in the event that someone is late. They should reflect 2 payments due, not just 1.

 

[My Aswer Is This]

If you want something to happen you normally have to say when this happens do this.

So what must happen to have the row copied to the next empty row.

Would it be when you enter a value some place in the row. Or when you activate the sheet or ??

 

[reberryjr]

Originally, I was thinking that I could tie it to a button...something like "Calculate Next Payment Due". Then the User could somewhat create the rows when they want to. The issue I think would happen is, what if it's not done frequently? Would there be due dates that aren't calculated because the timing of the next due date doesn't coincide with when the button was clicked. I gave thought to giving every Client their own tab, but ranges would become very messy to create and keep up to date as new Clients come on board.

 

[The_Macrotect]

With any cell in the last "Jennifer Chu" row selected, run the code below. Does this give you the desired result? If so, we can add to this to make it work for all customers based on their payment terms.

Sub Add_Row_Jennifer()
'
' Add_Row Macro
'

'
Dim d As Date
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 12).Select
d = DateAdd("m", 1, ActiveCell.Value)
ActiveCell.Offset(1, -3).Select
ActiveCell.Value = d
End Sub

 

[reberryjr]

@The_Macrotect, thank you for the response! I ran that code and it inserted the row and pasted data into the new row. The DP Next Due reads "08/18/00" instead of 10/04/17. I also need to correct something I said, and advise of an aspect I left out of my earlier information. I don't want "everything" copied over. For example, I wouldn't want the DP Pymt Date copied, since the new payment may not have been made yet. Also, there are 5 sets of services offered. All of them have the same criterion as the last 8 columns in the table I provided, with only the first 2 characters being different. It was a large spreadsheet example to paste into here, so I didn't. These other services could have different due dates, amounts and payment frequencies, or none at all.

The VBA work I've done thus far has really been limited to entering data with UserForms, sorting and adding some formulas. I suspect that Excel isn't the best application for what I'm trying to do, but since my work frowns on Access, I'm kind of left without many options.

 

[The_Macrotect]

@The_Macrotect, thank you for the response! I ran that code and it inserted the row and pasted data into the new row. The DP Next Due reads "08/18/00" instead of 10/04/17. I also need to correct something I said, and advise of an aspect I left out of my earlier information. I don't want "everything" copied over. For example, I wouldn't want the DP Pymt Date copied, since the new payment may not have been made yet. Also, there are 5 sets of services offered. All of them have the same criterion as the last 8 columns in the table I provided, with only the first 2 characters being different. It was a large spreadsheet example to paste into here, so I didn't. These other services could have different due dates, amounts and payment frequencies, or none at all.

The VBA work I've done thus far has really been limited to entering data with UserForms, sorting and adding some formulas. I suspect that Excel isn't the best application for what I'm trying to do, but since my work frowns on Access, I'm kind of left without many options.

Sorry, it looks like I left out a column when copying over the table, which caused the error. I've fixed it on my end. Which columns should be copied over, or if it's easier, which columns should not be copied?

I'm about to visit Mom and have dinner, so I'll take a look afterwords.

 

[reberryjr]

Enjoy dinner and the time with your Mom!

The columns that should NOT copy down are DP Pymt Date and DP Paid.[TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl65, width: 89"][/TD]
[TD="class: xl66, width: 54"][/TD]
[/TR]
</tbody>[/TABLE]

 

[The_Macrotect]

Hey, just wanted to let you know I've been working on it, and I think I'm almost there. I've got it to the point where it can insert a new row after the last row for a specific customer, and am almost done getting it to copy everything except the cells you said not to include from the last row.

By the way, would you like the "Updated" and "DP Pymt Status" cells to be pasted to the new row, or to be left out?

I'm off to bed, but let me know and I'll pick up again tomorrow after work.