Min/Max

charlee63

Board Regular
Joined
Jan 7, 2010
Messages
151
Office Version
  1. 2013
Data
1739822481714.png

Found a function to do 1 criteria but I need to find a Function that can use 2 criteria. Need to have min or max of Criteria 1 = Column J and Criteria 2 = Column E Giving me the result from Column H(Min)/I(Max).
I need to do this in a VBA. I will be changing the Start date in column H to the Min on the code and changing the End Date Max on the code to Max.

So for code 177591 and group link 123, Min = 2/2/25 and Max =2/22/25

Sub JUNK2()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MyMin = minMax(Range("E2:E" & LastRow), "max")
End Sub
Function minMax(ByVal rRange As Range, MinOrMax As String) As Double
Dim dMin As Double
Dim dMax As Double
Dim lLastRow As Long
Dim ws1 As Worksheet

Set ws1 = ActiveWorkbook.ActiveSheet
lLastRow = ws1.Cells(rRange.Row, rRange.Column).End(xlDown).Row

dMin = ws1.Cells(rRange.Row, rRange.Column).Value
dMax = dMin

For Each Cell In rRange.Cells
If Cell.Value < dMin Then dMin = Cell.Value
If Cell.Value > dMax Then dMax = Cell.Value
Next Cell

If InStr(1, MinOrMax, "min") = 1 Then
minMax = dMin
Else
minMax = dMax
End If

End Function
 
What version of Excel are you using?

The newer version have MINIFS and MAXIFS function, which make this much easier (can have multiple conditions).
See here: MAXIFS function - Microsoft Support
and here: MINIFS function - Microsoft Support

You can use both in VBA by prefacing it with "Application.WorksheetFunction.", i.e.
VBA Code:
x= Application.WorksheetFunction.MinIfs(...)

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
OK, you will find the best way to get people to help you is make it easy for them to help. There are 3 main "tools" that can help with that.

1. Update your Office version in your profile so we know what version you are using (you have done that now, so thank you).

2. Use Code Tags when posting your code. This makes your code much easier for people to read and understand (see: How to Post Your VBA Code).

3. Post sample data in a way that allow users to easily and copy and paste it to their side, so they do not have to manually type it all in themselves (many will not bother and just move on to other questions). Images do not allow that. There is a tool you can download and use here which can aid in posting sample data in a way that allows users to easily copy/paste it here. See this: XL2BB - Excel Range to BBCode. If you are unable to use that, at least do a copy/paste of your data range. It may not be the prettiest thing, but should allow us to copy/paste it to our side.
 
Upvote 0
Note that for your User Defined Function, you will need more arguments, i.e.

1 - Range1
2 - Criteria for Range1
3 - Range2
4 - Criteria for Range2
5 - Range to return Min/Max value for
6 - Min or Max

As you loop through each row, you will need to do a IF statement to check to see if that rows meets your two criteria.
If it does, then you can do your MIN/MAX check on it. Otherwise, move on to the next row.
 
Upvote 0
GM-HBC Flyer - 2508 02-22-25.xlsm
ABCDEFGHIJ
1DivisionGLAP #AP_Vendor_NameCodeDescriptionVendor BillbackStartEnd Group Link
254NE3100448PROCTER & GAMBLE177591SATIN CARE SENSITIVE GEL0.192/6/20252/22/2025123
255NE3100448PROCTER & GAMBLE346032SATIN CARE SENSITIVE GEL0.192/8/20252/21/2025123
256NE3100448PROCTER & GAMBLE177591SATIN CARE SENSITIVE GEL0.192/8/20252/21/2025123
257NE3100448PROCTER & GAMBLE177591SATIN CARE SENSITIVE GEL0.192/2/20252/21/2025123
Ad
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E1:E1072,E1802:E1048576Expression=COUNTIFS($E:$E,E1,$A:$A,$A1,$J:$J,$J1)>1textYES
 
Upvote 0
OK, here is the code I came up with:
VBA Code:
Sub Test1()
    
    Dim LastRow As Long
    Dim MyMin As Variant
    Dim myMax As Variant
    
'   Determine range from column E
    LastRow = Cells(Rows.Count, "E").End(xlUp).Row
    MyMin = MinMax2(Range("I254:I" & LastRow), "E", 177591, "J", 123, "Min")
    myMax = MinMax2(Range("I254:I" & LastRow), "E", 177591, "J", 123, "Max")
    
'   Return max value
    MsgBox "Min: " & Format(MyMin, "m/d/yyyy") & vbCrLf & "Max: " & Format(myMax, "m/d/yyyy")
        
End Sub


Function MinMax2(mRng As Range, col1 As String, crit1 As Variant, col2 As String, crit2 As Variant, MinOrMax As String) As Variant
'   mRng = range to pull min/max from
'   col1 = column letter of first criteria
'   crit1 = criteria for first range
'   col2 = column letter of second criteria
'   crit2 = criteria for second range
'   MinOrMax = tell function to return "min" or "max"

    Dim cell As Range
    Dim ct As Long
    Dim dMin As Double
    Dim dMax As Double

'   Loop through each cell in range
    For Each cell In mRng
'       Check to see if criteria matches
        If (Cells(cell.Row, col1) = crit1) And (Cells(cell.Row, col2) = crit2) Then
'           Add one to match counter
            ct = ct + 1
'           Set initial values
            If ct = 1 Then
                dMax = cell.Value
                dMin = cell.Value
            Else
'               Check and set values
                If cell.Value > dMax Then dMax = cell.Value
                If cell.Value < dMin Then dMin = cell.Value
            End If
        End If
    Next cell
    
'   Check to see if any matches
    If ct > 0 Then
'       Determine to pull Min or Max
        Select Case UCase(MinOrMax)
            Case "MAX"
                MinMax2 = dMax
            Case "MIN"
                MinMax2 = dMin
            Case Else
                MinMax2 = "ERROR: Invalid MinOrMax variable!"
        End Select
    Else
        MinMax2 = "No matches found in range!"
    End If

End Function

And here is my proof of concept:

1739881573971.png
 
Upvote 0
Solution
OK, here is the code I came up with:
VBA Code:
Sub Test1()
   
    Dim LastRow As Long
    Dim MyMin As Variant
    Dim myMax As Variant
   
'   Determine range from column E
    LastRow = Cells(Rows.Count, "E").End(xlUp).Row
    MyMin = MinMax2(Range("I254:I" & LastRow), "E", 177591, "J", 123, "Min")
    myMax = MinMax2(Range("I254:I" & LastRow), "E", 177591, "J", 123, "Max")
   
'   Return max value
    MsgBox "Min: " & Format(MyMin, "m/d/yyyy") & vbCrLf & "Max: " & Format(myMax, "m/d/yyyy")
       
End Sub


Function MinMax2(mRng As Range, col1 As String, crit1 As Variant, col2 As String, crit2 As Variant, MinOrMax As String) As Variant
'   mRng = range to pull min/max from
'   col1 = column letter of first criteria
'   crit1 = criteria for first range
'   col2 = column letter of second criteria
'   crit2 = criteria for second range
'   MinOrMax = tell function to return "min" or "max"

    Dim cell As Range
    Dim ct As Long
    Dim dMin As Double
    Dim dMax As Double

'   Loop through each cell in range
    For Each cell In mRng
'       Check to see if criteria matches
        If (Cells(cell.Row, col1) = crit1) And (Cells(cell.Row, col2) = crit2) Then
'           Add one to match counter
            ct = ct + 1
'           Set initial values
            If ct = 1 Then
                dMax = cell.Value
                dMin = cell.Value
            Else
'               Check and set values
                If cell.Value > dMax Then dMax = cell.Value
                If cell.Value < dMin Then dMin = cell.Value
            End If
        End If
    Next cell
   
'   Check to see if any matches
    If ct > 0 Then
'       Determine to pull Min or Max
        Select Case UCase(MinOrMax)
            Case "MAX"
                MinMax2 = dMax
            Case "MIN"
                MinMax2 = dMin
            Case Else
                MinMax2 = "ERROR: Invalid MinOrMax variable!"
        End Select
    Else
        MinMax2 = "No matches found in range!"
    End If

End Function

And here is my proof of concept:

View attachment 122481
Thanks
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

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