Thanks:  0
Likes:  0

# Thread: Max/Min in VB using worksheet database

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

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

5. Very nice!

## User Tag List

#### Posting Permissions

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