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]

 

[Fluff]

I think were both getting a bit lost here.
1) Your dictionary reads all client accounts from StatsClientID into cobo_ClientID If StatsClientID is dynamic, that means you have a dynamic list of clients in the combobox. As long as the sheet names are the same as the client Ids then you have a dynamic list of sheet names. Are you happy with this?

2) Assuming you are only updating 1 client at a time on the userform, then you can use something like I posted in post#25 to find the last/next row in the relevant sheet. Are you happy with this?

3) Could you show us how your sheets are laid out. I have no idea what you mean by

it isn't tying to the max updated date

 

[reberryjr]

Sorry about that. It's probably me not explaining what I'm trying to do correctly. Here's a level view:
1. A 3 character Client ID (ex. RB1, RB2, QJ4) is assigned when a new Client is added. (Done. This is housed on a sheet entitled "Bios" and has a dynamic range tied to it.)
2. A separate sheet is created for each Client, named the same as their Client ID. (Done.)
3. When the User needs to update the financials on a Client's sheet, I want them to:

3.a. Click a command button that launches a UserForm. (Done.)
3.b. Select the appropriate Client Id from cobo_ClientID. (Done.)
3.c. The UserForm would then identify the correct sheet (where the sheet name equals the cobo_ClientID value). (Here's where I'm struggling.)
3.d. The UserForm pulls in the data elements present on the UserForm, from the max Updated date. (I think I can do this if I can figure out step 3.c.)​

As the User makes updates to each Client sheet (individually), a new row would be created to house the updates. Each time, the Updated field gets a date, letting me identify the MAX updated record.

The unique Client sheets are laid out like this. Note that there are 4 more sets of data that are similar to columns F:O, with slightly different column headers.
[TABLE="width: 1044"]
<tbody>[TR]
[TD="class: xl63, width: 61"]Today[/TD]
[TD="class: xl63, width: 61"]Updated[/TD]
[TD="class: xl64, width: 45"]Status[/TD]
[TD="class: xl64, width: 60"]Client ID[/TD]
[TD="class: xl64, width: 105"]Name[/TD]
[TD="class: xl64, width: 65"]DP Status[/TD]
[TD="class: xl63, width: 61"]DP Start[/TD]
[TD="class: xl64, width: 88"]DP Pymt Amt[/TD]
[TD="class: xl63, width: 85"]DP Next Due[/TD]
[TD="class: xl64, width: 55"]DP Freq[/TD]
[TD="class: xl64, width: 101"]DP Pymt Status[/TD]
[TD="class: xl64, width: 54"]DP Paid[/TD]
[TD="class: xl63, width: 75"]DP Paid On[/TD]
[TD="class: xl64, width: 78"]DP Net Due[/TD]
[TD="class: xl63, width: 50"]DP End[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

OK for 3.c try something like this

Private Sub cobo_ClientID_Change()
   Dim Sht As String
   
   Sht = Me.cobo_ClientID.Value
   [COLOR=#ff0000]TextBox1[/COLOR].Value = Sheets(Sht).Range("A5").Value
   
End Sub

Changing textbox name & range accordingly

 

[reberryjr]

That works great to pull in the correct name. I tried to code to get the other fields, using this code and I'm getting an "Invalid Qualifier" error at the red font.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long


Sht = Me.cobo_ClientID


LastRow = [COLOR=#ff0000]Sht[/COLOR].Range("E" & RowsCount).End(xlUp).Row


txt_Name = Sheets(Sht).Range("E2").Value
txt_DPAmt = Sheets(Sht).Range("H" & LastRow).Value






End Sub

 

[Fluff]

That should be done the same as 2 lines below

 

[reberryjr]

I'm sorry, but what 2 lines? In thinking about it, I could make looking at row 2 work if I sort each sheet by the updated column, in descending order. I'll just have to account for that in the next phase, when I add formulas to calculate gains and losses.

 

[Fluff]

Apologies, that wasn't very clear.
I meant the next 2 lines in your code, ie Sheets(sht)

 

[reberryjr]

I stepped through the code and it was failing at that spot in red font. However, I "think" I may have gotten it with this code. I've tested it with two values on two test sheets and it seems to be working so far. I think the issue was that the way I was coding for LastRow in other places, had extra variables that aren't really required and I was being thrown off.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long


Sht = Me.cobo_ClientID


With ActiveSheet
    LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With


txt_Name = Sheets(Sht).Range("E").Value
txt_DPPymtAmt = Sheets(Sht).Range("H" & LastRow).Value






End Sub