Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Min value in range with VBA?
I know this is pretty basic, but it's been a long night...
How do I find the minimum value in a range? If I have Q2:Q12, and want to find the minimum value - via automation from Access, would it look something like this: dblMin = xlapp.Evaluate("MIN(Q2:Q12)") ??? same for Max ?? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Min value in range with VBA?
deko,
This works: Dim dblMin As Double Dim xlApp As Object Dim xlBook As Excel.Workbook Dim xlSht As Excel.Worksheet Set xlApp = CreateObject("excel.application") Set xlBook = xlApp.Workbooks.Open("C:\Path\Filename.xls") Set xlSht = xlBook.ActiveSheet dblMin = xlApp.WorksheetFunction.Min(xlSht.Range("Q2:Q12")) MsgBox dblMin This requires a reference to your Excel application through Tools / References (in the VBE). HTH, Bernie MS Excel MVP "deko" wrote in message . com... I know this is pretty basic, but it's been a long night... How do I find the minimum value in a range? If I have Q2:Q12, and want to find the minimum value - via automation from Access, would it look something like this: dblMin = xlapp.Evaluate("MIN(Q2:Q12)") ??? same for Max ?? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Min value in range with VBA?
This works:
Dim dblMin As Double Dim xlApp As Object Dim xlBook As Excel.Workbook Dim xlSht As Excel.Worksheet Set xlApp = CreateObject("excel.application") Set xlBook = xlApp.Workbooks.Open("C:\Path\Filename.xls") Set xlSht = xlBook.ActiveSheet dblMin = xlApp.WorksheetFunction.Min(xlSht.Range("Q2:Q12")) MsgBox dblMin This requires a reference to your Excel application through Tools / References (in the VBE). Thanks! That really helped. Here's what seems to be working for me (still testing)... dblMin = xlapp.WorksheetFunction.Min(xlapp.Workbooks(strXls File).Worksheets(i).Range( strRange)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Min value in range with VBA?
That should more than seem to work - I just didn't know how you were
referencing the workbook and worksheet objects. HTH, Bernie MS Excel MVP Thanks! That really helped. Here's what seems to be working for me (still testing)... dblMin = xlapp.WorksheetFunction.Min(xlapp.Workbooks(strXls File).Worksheets(i).Range( strRange)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
Find the MAX number in range, then find... | Excel Discussion (Misc queries) | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
Find first cell in range and expand range -VBA | Excel Programming |