Max/Min in VB using worksheet database
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Max/Min in VB using worksheet database

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to calculate a Max and Min value from an excel database in my VB code. Is there a way to calulate while specifying a criteria. For example if I want the Max value of UnitsSold for Region = "West", calculated within my VB Code using a database that resides on an Excel sheet?
    Thanks in advance for any assistance.

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 15:51, sb fredster wrote:
    I am trying to calculate a Max and Min value from an excel database in my VB code. Is there a way to calulate while specifying a criteria. For example if I want the Max value of UnitsSold for Region = "West", calculated within my VB Code using a database that resides on an Excel sheet?
    Thanks in advance for any assistance.
    There are a few ways to do it, but the easiest is probably just using a Worksheet Function. Pick a cell where you have no data and use it. Something like this (say your Regions are in cells B8:B13, and the corresponding values in C8:C13):

    Code:
    Option Explicit
    
    Sub FindMyMin()
        Dim MyMin As Double
        
        ' I just used a cell that was way out of the way...
        ActiveSheet.Range("IV1").FormulaArray = _
           "=MIN(IF(B8:B13=""West"",C8:C13))"
               
        MyMin = ActiveSheet.Range("IV1")
        ActiveSheet.Range("IV1").ClearContents
        
        ' etc., etc.
    End Sub
    You could also use an array, but you would have to process both the "West" part, and also the min value part.




  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 16:33, Russell Hauf wrote:
    On 2002-03-22 15:51, sb fredster wrote:
    I am trying to calculate a Max and Min value from an excel database in my VB code. Is there a way to calulate while specifying a criteria. For example if I want the Max value of UnitsSold for Region = "West", calculated within my VB Code using a database that resides on an Excel sheet?
    Thanks in advance for any assistance.
    There are a few ways to do it, but the easiest is probably just using a Worksheet Function. Pick a cell where you have no data and use it. Something like this (say your Regions are in cells B8:B13, and the corresponding values in C8:C13):

    Code:
    Option Explicit
    
    Sub FindMyMin()
        Dim MyMin As Double
        
        ' I just used a cell that was way out of the way...
        ActiveSheet.Range("IV1").FormulaArray = _
           "=MIN(IF(B8:B13=""West"",C8:C13))"
               
        MyMin = ActiveSheet.Range("IV1")
        ActiveSheet.Range("IV1").ClearContents
        
        ' etc., etc.
    End Sub
    You could also use an array, but you would have to process both the "West" part, and also the min value part.



    A small addition...

    You can use the Evaluate method rather than working with a worksheet cell.

    MyMin = Evaluate("=MIN(IF(B8:B13=""West"",C8:C13))")

    Evaluate considers all the expressions as arrays, if needed, so you don't have to expressly state the .FormulaArray entry when using this.

    From a purist's perspective, Evaluate() is a cheat, but I really like it.

    Bye,
    Jay

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I LOVE the Evaluate Method, however it suffers from the same problem as the .FormulaArray property. The length of the string it is passed CAN NOT be longer than 255 chars... why ? really strange. Microsoft solution ?

    "Don't use an array formula with more than 255 characters"...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Very nice!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com