On Fri, 9 Dec 2005 07:47:30 -0500, "JMay" wrote:

I'm running excel 2003.
I referenced a small range A2:A8; containing
4,6,9,7,5,8,2=MinCellAddr(A2:A8)


Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address
End Function

I don't know why you are getting the VALUE error.

I typed your numbers into A2:A8

I copied the code you just posted, and pasted into a module.

To open the module, I ensured the proper project was highlighted in the Project
Explorer window of the VB Editor. I then selected Insert/Module from the top
menu bar, and just pasted in the code you posted (which is a copy of mine).

I then pasted the =MinCellAddr(A2:A8) function into A1 and it returned $A$8

I'm not sure what's happening at your machine. But try this code with an
expanded FIND function:

Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(What:=MinNum, _
LookIn:=xlValues, LookAt:=xlWhole, _
searchorder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False, matchbyte:=False).Address
End Function

If that doesn't work, we'll have to debug the code on your machine, to see
where it's going wrong.

As a first step, you could place a breakpoint next to the "End Function" line
to see if the function is even completing. If it is, then there may be
something unexpected about your data. If it is not completing. then place
breakpoints next to each line to narrow down the location of the issue.


--ron