VBA Function Max Value of Matching Columns

[Stephen_IV]

Good evening I have data in 2 columns. In my process to continually learn VBA, I kind of got stuck. I would like to know If the data matches then I would like to get the Max value of the matches. I have a formula: =SUMPRODUCT(--(MAX(IF(A1:A38=B1:B38,A1:A38,"")))) that seems to work but I am looking for a VBA function. Can anyone please assist. Thanks in advance. Book5 A B C D E F G 1 6 6 11 2 10 10 3 11 8 4 13 9 5 9 9 6 10 10 7 19 4 8 16 15 9 6 6 10 12 11 11 14 7 12 19 11 13 6 6 14 7 7 15 9 9 16 6 6 17 11 11 18 12 9 19 6 5 20 15 10 21 7 7 22 7 7 23 7 6 24 13 11 25 11 8 26 9 6 27 13 10 28 8 7 29 14 11 30 7 7 31 11 9 32 6 6 33 18 17 34 8 8 35 6 1 36 17 16 37 7 7 38 9 9 Sheet1 Cell Formulas Range Formula G1 G1 =SUMPRODUCT(--(MAX(IF(A1:A38=B1:B38,A1:A38,""))))

 

[StephenCrump]

In my process to continually learn VBA, I kind of got stuck...

It sounds like you'd prefer to get your code working, rather than have someone give you code (that might do the job differently)?

What code do you have, and where are you getting stuck?

 

[Stephen_IV]

Thanks StephenCrump,
I will take anyone code. Any help would be appreciated.

 

[StephenCrump]

OK, here's one way:

Function MyMaxIf(rng As Range, Optional comparator = "=") As Variant

    MyMaxIf = CVErr(xlErrNA)
    
    If rng.Columns.Count = 2 Then _
        MyMaxIf = Evaluate("MAX(IF(" & rng.Columns(1).Address(, , , 1) & comparator & rng.Columns(2).Address(, , , 1) & "," & rng.Columns(1).Address(, , , 1) & "))")

End Function

 

[Stephen_IV]

Thanks StephenCrump,

That seems to do the trick! I appreciate your help & time on this post!

 

[planetpj]

Here is another way. Must add reference, Tools, References, Microsoft Scripting Runtime

Function MaxMatch(r As Range)
Dim dic As New Dictionary
If r.Columns.Count = 2 Then
    For Each xcell In r
        If xcell.Value = xcell.Offset(0, 1).Value Then
            If xcell <> "" Then
                If Not dic.Exists(xcell.Value) Then
                    dic.Add xcell.Value, Nothing
                    xmax = Application.Max(dic.Keys, xmax)
                End If
           End If
       End If
    Next
End If
Dim k
    For Each k In dic.Keys
        If k = xmax Then
           MaxMatch = k
        End If
    Next
End Function

 

[Stephen_IV]

Thanks PlanetPJ! Work great! Thanks you and StephenCrump for helping me learn different approaches to programming. You both have a great day!