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.