VBA Function Max Value of Matching Columns

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,168
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEFG
16611
21010
3118
4139
599
61010
7194
81615
966
101211
11147
121911
1366
1477
1599
1666
171111
18129
1965
201510
2177
2277
2376
241311
25118
2696
271310
2887
291411
3077
31119
3266
331817
3488
3561
361716
3777
3899
Sheet1
Cell Formulas
RangeFormula
G1G1=SUMPRODUCT(--(MAX(IF(A1:A38=B1:B38,A1:A38,""))))
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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?
 
Upvote 0
Thanks StephenCrump,
I will take anyone code. Any help would be appreciated.
 
Upvote 0
OK, here's one way:

Code:
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
 
Last edited:
Upvote 0
Thanks StephenCrump,

That seems to do the trick! I appreciate your help & time on this post!
 
Upvote 0
Here is another way. Must add reference, Tools, References, Microsoft Scripting Runtime

VBA Code:
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
 
Upvote 0
Thanks PlanetPJ! Work great! Thanks you and StephenCrump for helping me learn different approaches to programming. You both have a great day!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top