LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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.
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
minimum value in a column, which row no? Darius Excel Worksheet Functions 6 April 22nd 09 06:53 PM
minimum value column name look up shifty Excel Discussion (Misc queries) 2 December 17th 07 02:34 PM
Find minimum value in column. dlbeiler Excel Worksheet Functions 3 October 12th 07 05:55 PM
How to get the minimum number of one column while ignoring the err Yuanhang Excel Discussion (Misc queries) 2 June 30th 07 03:06 PM
Header of column which has minimum in row? PGPS Excel Discussion (Misc queries) 1 November 28th 06 09:03 PM


All times are GMT +1. The time now is 03:41 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"