CODEWITHSUNDEEP
vbafunctionsumifsranged-loops
draynaud
draynaud

Reputation: 63

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

Upvotes: 1

Views: 98

Answers (2)

draynaud
draynaud

Reputation: 63

For now i am using a very basic and SLOW solution:

Function max_table(table As range, col1 As range, c1 As range, col2 As range, c2 As range)
Dim i, j As Integer
Dim prov As Integer

max_table = 0
For i = 1 To table.Columns.Count
prov = 0
For j = 1 To table.Rows.Count
    If col1(j).Value = c1.Value And col2(j).Value = c2.Value Then
    prov = prov + table.Cells(j, i).Value
    End If
Next j
If max_table < prov Then
max_table = prov
End If
Next i

End Function

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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

Upvotes: 2

Related Questions