Min/Max

[charlee63]

Data

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

 

[Joe4]

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.

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’)

 

[charlee63]

2013 Which does not have those.

 

[Joe4]

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.

 

[Joe4]

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.

 

[charlee63]

GM-HBC Flyer - 2508 02-22-25.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Division	GL	AP #	AP_Vendor_Name	Code	Description	Vendor Billback	Start	End	Group Link
254	NE	3100	448	PROCTER & GAMBLE	177591	SATIN CARE SENSITIVE GEL	0.19	2/6/2025	2/22/2025	123
255	NE	3100	448	PROCTER & GAMBLE	346032	SATIN CARE SENSITIVE GEL	0.19	2/8/2025	2/21/2025	123
256	NE	3100	448	PROCTER & GAMBLE	177591	SATIN CARE SENSITIVE GEL	0.19	2/8/2025	2/21/2025	123
257	NE	3100	448	PROCTER & GAMBLE	177591	SATIN CARE SENSITIVE GEL	0.19	2/2/2025	2/21/2025	123
Ad

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E1:E1072,E1802:E1048576	Expression	=COUNTIFS($E:$E,E1,$A:$A,$A1,$J:$J,$J1)>1	text	YES

 

[Joe4]

OK, here is the code I came up with:

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:

 

[charlee63]

OK, here is the code I came up with:

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

 

[Joe4]

You are welcome.
Glad I was able to help!