Find Min value in range with VBA?

  • Thread starter Thread starter deko
  • Start date Start date
D

deko

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.
 
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
 
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).
Click to expand...

Thanks! That really helped. Here's what seems to be working for me (still
testing)...

dblMin =
xlapp.WorksheetFunction.Min(xlapp.Workbooks(strXlsFile).Worksheets(i).Range(
strRange))
 
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
 
Back
Top