Minimum value in a column
 

Could someone tell me how to use VBA to find the minimum value in
column containing integers.

Regards,
Ala

--
Message posted from http://www.ExcelForum.com

Minimum value in a column
 

Alan,

Try something like the following:

Dim Res As Long
Res = Application.WorksheetFunction.Min(Range("A1:A10"))
Debug.Print Res


Change the reference A1:A10 to your desired range.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"AlanDavidson "
wrote in message
...
Could someone tell me how to use VBA to find the minimum value
in a
column containing integers.

Regards,
Alan


---
Message posted from http://www.ExcelForum.com/

Minimum value in a column
 

Thanks Chip. That worked fine.

Regards,
Alan


-----Original Message-----
Alan,

Try something like the following:

Dim Res As Long
Res = Application.WorksheetFunction.Min(Range("A1:A10"))
Debug.Print Res


Change the reference A1:A10 to your desired range.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"AlanDavidson " <<AlanDavidson.168sv7@excelforum-
nospam.com
wrote in message
...
Could someone tell me how to use VBA to find the
minimum value
in a
column containing integers.

Regards,
Alan


---
Message posted from http://www.ExcelForum.com/



.

Minimum value in a column
 

Here are two ways to do it. There must be many mo

Name the range containing the intergers "Foo"

Sub foo()
Dim rng As Range
Dim cell As Object
Dim i As Integer
Set rng = Range("Foo")
i = rng(1)
With rng
For Each cell In rng.Cells
If cell.Value < i Then
i = cell.Value
End If
Next
End With
Debug.Print i
Set rng = Nothing
End Sub

This simply assigns the first value in range "Foo" to i, then tests the values
in each cell in range "Foo". If that value is less than i, i gets reassigned,
otherwise i remains the same and the loop commences.

Here's another way, using the evaulate method:

Sub foo2()
Dim rng As Range
Dim i As Integer
Set rng = Range("foo")
i = Evaluate("Min(" & rng.Address & ")")
Debug.Print i
End Sub

Here, you borrow Excel's built-in Min function using the Evaluate method.
I'm not sure which method is preferrable, but in the interests of typing less,
maybe the second approach is best.

HTH,
Johnny

Other readers: Do you know how to convert the Evaluate method above to the
shorthand version using square brackets? I couldn't get it to work.