vbafunctionsumifsranged-loops

VBA: Function comparing SUMIFS on different columns of a range and returning Min or Max according to a boolean

I am trying to get to a sumifs with two parameters on an important set of columns (# of columns won´t change, data will), and then return the max or the min 

Function min_or_max(B As Boolean, table As Range, col1 As Range, c1 As Cell, col2 As Range, c2 As Cell)
Dim column As Range

'If B = False Then
min_or_max = 0
For Each column In Range.Columns
If ActiveSheet.WorksheetFunction.SumIfs(Range.column, col1, c1, col2, c2) > min_or_max Then
min_or_max = ActiveSheet.WorksheetFunction.SumIfs(Range.column, col1, c1, col2, c2)
End If
Next column

'Elsif B = True
'min_or_max = 10000000
'For Each column In Range.Columns
'If ActiveSheet.WorksheetFunction.SumIfs(Range.Columns(i), col1, c1, col2, c2) < min_or_max Then
'min_or_max = ActiveSheet.WorksheetFunction.SumIfs(Range.Columns(i), col1, c1, col2, c2)
'End If
'Next column
End Function

Before even do the split min or max, I can´t get the SUMIFS to work. Is it because of bad argements entry? (i hope that clear!)

Thanks a lot to whoever can help me! drf

Solution

  • The problem is WorksheetFunction is not a Worksheet object, but an Application object.

    So, you would want to use Application.Worksheetfunction

    This line:

    If ActiveSheet.WorksheetFunction.SumIfs(Range.column, col1, c1, col2, c2) > min_or_max Then

    Needs to look like this:

    If Application.WorksheetFunction.SumIfs(Range.column, col1, c1, col2, c2) > min_or_max Then