On 9 Dec 2005 17:20:12 -0800, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
Since I see this post in programming, I will assume you want a VBA solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
...

Already pointed out .Find fails when called in udfs in XL97, but
irrelevant to OP.

Any idea why he's having what seems like a problem with the VBA Find function
in XL2003?



The OP's message was crossposted to w.f as well, so another formula
approach. If the range in question, which I'll denote RNG, is 1D, then

=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),RNG,0) ))

I could not get this to work with a two column range. It seems MATCH does not
like a 2 column array (xl2002) on my machine.


should return the address of the cell containing the first instance of
the minimum value. If RNG could be 2D, then if there could be multiple
instances of the minimum value, which should be considered the first
instance: the one in the leftmost column or the one in the topmost row?

Leftmost column (array formula):
=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),INDEX(RN G,0,
MIN(IF(RNG=MIN(RNG),COLUMN(RNG)))),0),MIN(IF(RNG= MIN(RNG),COLUMN(RNG)))))

Topmost row (array formula):
=CELL("Address",INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW (RNG))),
MATCH(MIN(RNG),INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW( RNG))),0),0)))


--ron