Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Maximum value when... (2007)

    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!!!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum value when... (2007)

    Could you provide a small sample workbook?

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum value when... (2007)

    Sure thing. I have deleted a lot of data from other coulmns, leaveing the ones that are salient to this need.
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum value when... (2007)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •