Formulas Entered into Sheets via UserForm

[reberryjr]

Here's my challenge. When the EU (End User) is adding a new Client, they do so via a UserForm (frm_AddNew). When they click the cmd_Submit button, the UserForm data is entered onto various sheets, and a new sheet is created to hold their Service information. This new sheet is named after their assigned Client ID. Because I want to limit the data that the EU actually "touches", I want key information to map over to the Client Summaries sheet.

Goal 1 - I'd like to create a formula that can be added to column F on the Client Summaries sheet, that will identify the first instance of column BO on the particular Client's sheet. In the attached, I would want F2 on the Client Summaries sheet to reflect 14 from column BO on sheet TJ1. I do have some code that does some of this, dealing with other sheets. I tried to leverage that code in Macro3, but it isn't working.
Challenge - Implementing code that's going to know what sheet to go to, find column BO and enter the first instance where the value is >0. If there is no value > 0, then return a value of 0.

Goal 2 - Accurately calculate the difference in fields on the Stats tab (Ex: Weight). The calc should be the value of the last instance of each Client ID, from the first instance of each Client ID (column C). On the attached, it would be H5-H3. The Stats sheet is already coded to auto-sort by Client ID, then by Update Date when the sheet is activated, so it should always be in the proper order. The expectation is that I5 on sheet TJ1, would read -30.00.
Challenge - Implementing code that identifies the min update date, the max update and then calculates the value of column H between the two rows.

Hopefully the goals and challenges are descriptive enough.

https://app.box.com/s/l3t6mxd0ex3dnp2k5ut2g5ynrd189p0i

 

[mumps]

Will the first instance of column BO on the Client's sheet always be in the second row? What happens when there is only one instance of a Client ID in the Stats sheet?

 

[reberryjr]

Yes it will. The value defaults to 0 if it is the first record.

 

[mumps]

Try these two macros:

Sub GetClientBO()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Client Summaries").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range
    For Each ID In Sheets("Client Summaries").Range("C2:C" & LastRow)
        ID.Offset(0, 3) = Sheets(ID.Value).Range("BO2")
    Next ID
    Application.ScreenUpdating = True
End Sub

Sub CalcDiff()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Stats").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range
    Dim rngUniques As Range
    Dim fVisRow As Long
    Dim lVisRow As Long
    Dim diff As Long
    Sheets("Stats").Range("C1:C" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("C1:C" & LastRow), Unique:=True
    Set rngUniques = Sheets("Stats").Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible)
    If Sheets("Stats").AutoFilterMode = True Then Sheets("Stats").AutoFilterMode = False
    For Each ID In rngUniques
        Sheets("Stats").Range("A1:AG" & LastRow).AutoFilter Field:=3, Criteria1:=ID
        fVisRow = Sheets("Stats").Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
        lVisRow = Cells(Rows.Count, "C").End(xlUp).Row
        Sheets(ID.Value).Cells(Rows.Count, "I").End(xlUp).Offset(1, 0) = Sheets("Stats").Range("H" & lVisRow).Value - Sheets("Stats").Range("H" & fVisRow).Value
    Next ID
    If Sheets("Stats").AutoFilterMode = True Then Sheets("Stats").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[reberryjr]

Holy cow! So, I tried the first macro and put some notes on the line that doesn't seem to be working. I'm still trying to understand the 2nd macro, but it isn't returning the value to the correct sheet. I'm going to keep trying to understand it, to see if I can figure it out.

Sub GetClientBO()    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Client Summaries").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range
    For Each ID In Sheets("Client Summaries").Range("C2:C" & LastRow)
        ID.Offset(0, 3) = Sheets(ID.Value).Range("BO2") 'The number of delinquent days may not always be in BO2.  Ideally _
        the code would search through all of BO, and return the first instance >0.  I changed the BO2 reference to BO2:BO _
        to no avail.
    Next ID
    Application.ScreenUpdating = True
End Sub

 

[mumps]

This is why I asked this question in Post #2 :

Will the first instance of column BO on the Client's sheet always be in the second row?

Could you post a copy of your actual file so that it has enough data in the appropriate sheets so that I can better idea of what we're dealing with. Most times what works with a sample file won't work with the actual file. De-sensitize it if necessary. Also explain how the second macro is not working.

 

[reberryjr]

@mumps , sorry about that. I must've misunderstood the question. I took it as asking me if the data would always start in BO2, not if the value I wanted to retrieve would be.

With respect to the first code, I found a formula that I almost got to work. It won't allow me to start the calculation on the 2nd row, through the whole column. It's forcing me to put in and end row.

The second code actually seems to be working, except it's entering the data in column I on the TJ1 sheet, instead of the Client Summaries sheet.

Again, my sincere apologies for my misunderstanding on the earlier question.

 

[mumps]

Could you upload a copy of your file as I requested in Post #6 ?

 

[reberryjr]

I found some other small issues with my code, when adding more data. I'm going to have to fix that and then upload a new file.

I appreciate your help and patience!

 

[reberryjr]

@mumps Hopefully this is enough additional data.

https://app.box.com/s/kn8u99uktmalyf1z88qax0mprztg0qqh