Maximum value when… (2007)

Topic

New Reply

Good day loungers!

I have a puzzler that has me stumped. I have a worksheet that has fiscal periods in column G and the Balance Type in Column AH. What I want to do in VBA is identify the maximum fiscal period when the balance Type is equal to AC. I then need to store that value to a variable that can be used in other formulas. There are 3 possible entries in the balance type column, AC, MB, and CB. The fiscal period will contain values 1-12. Is there anything similar to countif or sumif that can be used in this situation?

Thanks!!!

Reply | Quote

Replies

Could you provide a small sample workbook?

Reply | Quote

Sure thing. I have deleted a lot of data from other coulmns, leaveing the ones that are salient to this need.

Reply | Quote

In Excel itself, you can use an array formula for this (confirm with Ctrl+Shift+Enter):

=MAX(IF(AH2:AH1000=”AC”,G2:G1000))

If you want to do this in VBA, you have to use a loop:

Function MaxIfAC()
Dim r As Long
Dim m As Long
m = Range(“AH” & Rows.Count).End(xlUp).Row
MaxIfAC = 0
For r = 2 To m
If Range(“AH” & r) = “AC” And Range(“G” & r) > MaxIfAC Then
MaxIfAC = Range(“G” & r)
End If
Next r
End Function

Reply | Quote