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]

Easiest option for part 1 is to have the sheets named the same as the client ID.
As for part 2, not quite sure what you mean. If your just pulling data from 1 sheet, why do you need to get the last row on multiple sheets?

 

[reberryjr]

I think I have done the first part already. If the Client's Name is Rob Bowman and he's the first entry, his Client ID is RB1. Then Renee Bowman would be RB2 and so on. Is that what you're referring to? Easiest option for part 1 is to have the sheets named the same as the client ID.

As far as the LastRow, I would need that to ensure that the new records are being inserted at the bottom of the current data set, on each sheet. I suppose I could try and insert a row in row 2 and enter the data there, then sort the sheet.

 

[Fluff]

If the client ID is RB1 name the sheet RB1
To find the next row use something like this

   Dim NxtRw As Long
   NxtRw = Sheets("RB1").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[reberryjr]

Is there a way to code for the next or last row, so that as new sheets are created, new code doesn't have to also be created?

 

[Fluff]

This will find the next row on whatever sheet you select from the combobox

   Dim Sht As String
   Dim NxtRw As Long
   
   Sht = Me.cobo_ClientID.Value
   NxtRw = Sheets(Sht).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   MsgBox NxtRw

 

[reberryjr]

Ok...I'm completely lost. I'm not grasping the concept of how to code the scripting dictionary to read the correct sheet. Typically, all of the data is on one sheet, so I only have to reference that one sheet. With this task, I have to code for the User to select a Client ID on a form, that will then pull in the data from the sheet that has the same name as the Client ID. Example...Client ID = RB1, Sheet is named RB1. There are multiple entries on the RB1 Sheet, so the max Updated date should be leveraged. Is there a different concept that I should research, or a different approach I should take?

 

[Fluff]

You've got a dictionary that is populating a combobox, with the client ID, assuming that works, you can simply use the combobox value to refer to the sheet.

 

[reberryjr]

The dictionary that I've been using is tied to a specific sheet, not the dynamic list of sheets that needs to be referenced. It also references a specific range on said sheet.

 

[Fluff]

Does that range contain a list of ALL client Ids?

 

[reberryjr]

Yes it does, but it isn't tying to the max updated date on each unique sheet. Here it is.

Set coboDict = CreateObject("Scripting.Dictionary")With coboDict
    For Each cStatsClientID In ws1.Range("StatsClientID")
        If Not .exists(cStatsClientID.Value) Then
            .Add cStatsClientID.Value, cStatsClientID.Row
        Else
            If CLng(cStatsClientID.Offset(, -2).Value) > CLng(ws1.Range("B" & .Item(cStatsClientID.Value))) Then
            .Item(cStatsClientID.Value) = cStatsClientID.Row
            End If
        End If
    Next cStatsClientID
    Me.cobo_ClientID.List = Application.Transpose(.keys)
End With