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.

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.

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

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