Find Max Value by Group VBA

[JarekM]

Hello, I am having a problem that I can't resolve and could use some help. I have attached a screenshot and my code for reference. In the screenshot, in column "P" under Reference #, I currently have a working code that will find me the max value by group based on the first two letters, but I would also like to find the max value based on the project names under the "U" column. For an example, if I were to choose "PROJECT 4" then the reference numbers corresponding to "PROJECT 4" would find me the max value for reference number by the group.

Any help is appreciated.

Sub Create_RefNr(uf As Object)
        
        
                Set WbDb = Workbooks("DataBase.xlsx")
                Set WbCd = ThisWorkbook
                Set ShDb = WbDb.Sheets("TbExpense")
                        
'                Application.ScreenUpdating = False
                        
                WbDb.Activate
                ShDb.Activate
                Range("A1").Select
                
            If uf.cbo_RefNr = "PU" Or uf.cbo_RefNr = "RE" Or uf.cbo_RefNr = "DE" Or uf.cbo_RefNr = "TR" Then
 
                Dim i   As Long, a      As Long, b As Long
                Dim X   As String, txt  As String
                Dim va  As Variant, ar  As Variant
                Dim d   As Object
            
                Set d = CreateObject("scripting.dictionary")
                   va = ShDb.Range("p7", Cells(Rows.Count, "p").End(xlUp))
            
                    X = uf.cbo_RefNr.Text
                    On Error Resume Next
                    For i = 1 To UBound(va, 1)
                       ar = Split(va(i, 1), "-")
                        If UCase(ar(0)) = UCase(X) Then
                            d(CLng(ar(1))) = ""
                        End If
                    Next
                On Error Resume Next
                a = 1  'Application.Min(d.Keys)
                b = Application.Max(d.keys)
    
                uf.cbo_RefNr.Text = Left(uf.cbo_RefNr.Text & "-", 3) & Format(Right(b, 5) + 1, "00000")
    
        '        For i = a To b
        '          If Not d.Exists(i) Then txt = txt & ";" & i
        '        Next
        '        txt = Right(txt, Len(txt) - 1)
        '        Me.ListBox2.List = Split(txt, ";")
            End If
            '    WbDb.Close
    Set WbDb = Nothing
    Set ShDb = Nothing
    Set WbCd = Nothing

End Sub

 

[DanteAmor]

if I were to choose "PROJECT 4"

I assume that you are going to select the project from a combobox, so change cbo_project to the combo name:

Sub Create_RefNr(uf As Object)
  Dim b As Long, lr As Long
        
  Workbooks("DataBase.xlsx").Activate
  Sheets("TbExpense").Activate
  lr = Range("P" & Rows.Count).End(3).Row
  b = Evaluate("=MAX((LEFT(P7:P" & lr & ",2)=""" & uf.cbo_RefNr.Value & """)*" & _
               "(U7:U" & lr & "=""" & uf.cbo_Proj.Value & """)*(MID(P7:P" & lr & ",4,99)))")
  uf.cbo_RefNr.Text = Left(uf.cbo_RefNr.Text & "-", 3) & Format(Right(b, 5) + 1, "00000")
End Sub

Happy Holidays
Dante Amor

 

[JarekM]

I assume that you are going to select the project from a combobox, so change cbo_project to the combo name:

Sub Create_RefNr(uf As Object)
  Dim b As Long, lr As Long
      
  Workbooks("DataBase.xlsx").Activate
  Sheets("TbExpense").Activate
  lr = Range("P" & Rows.Count).End(3).Row
  b = Evaluate("=MAX((LEFT(P7:P" & lr & ",2)=""" & uf.cbo_RefNr.Value & """)*" & _
               "(U7:U" & lr & "=""" & uf.cbo_Proj.Value & """)*(MID(P7:P" & lr & ",4,99)))")
  uf.cbo_RefNr.Text = Left(uf.cbo_RefNr.Text & "-", 3) & Format(Right(b, 5) + 1, "00000")
End Sub

Happy Holidays
Dante Amor

Thank you, you're right in that I do select the project through a combobox. Your code works, but could you edit it so other than just finding the max value it would also show all of the values that are missing from Reference #?

Thanks Again
Merry Christmas to you too.

 

[DanteAmor]

You could document your new requirement, that is, explain with examples, what you are going to capture in combo cbo_RefNr, what you are going to capture in combo cbo_Proj, what you expect as a result and where you want the result.