Max/Min in VB using worksheet database

[sb fredster]

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.

[Russell Hauf]

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):

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.

[Jay Petrulis]

On 2002-03-22 16:33, Russell Hauf wrote:

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):

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

[Juan Pablo González]

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"...

[Russell Hauf]

Very nice!