Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minimum value in a column, which row no? | Excel Worksheet Functions | |||
minimum value column name look up | Excel Discussion (Misc queries) | |||
Find minimum value in column. | Excel Worksheet Functions | |||
How to get the minimum number of one column while ignoring the err | Excel Discussion (Misc queries) | |||
Header of column which has minimum in row? | Excel Discussion (Misc queries) |