Return of Cell Location

[William Wolfe]

I am running VB code in Excel 2007. I execute a min function and now I want
to know what cell contained the min value.

How do I do this?

Thanks,

 

[Gary''s Student]

Perhaps something like:

Sub FindMin()
Set r = Selection
v = Application.WorksheetFunction.Min(r)
For Each rr In r
If rr.Value = v Then
MsgBox (rr.Address)
Exit Sub
End If
Next
End Sub

 

[slarbie]

If you didn't want to use code, you could just apply a filter to the range
included in the MIN function and look for the MIN value in the filter
selection list.

 

[Chip Pearson]

Try some code like

Dim R As Range
Set R = Range("C5:C10") '<<< CHANGE
Set R = R.Offset(Application.Match( _
Application.Min(R), R, 0) - 1, 0).Resize(1, 1)
Debug.Print R.Address

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)