On Fri, 9 Dec 2005 15:47:02 -0800, "Jim May"
wrote:

Ron:
I set up both Screens side-by-side - Spreadsheet and VBE
On the original Code (Macro) I placed the Breakpoint next to the End
Function Line
On the spreadsheet Cell A1 (where =MinCellAddr(A2:A8) is the content I did an
Edit (F2) and re-entered it. AS I did the Cursor jumped to Cell A2, as
expected.
I then reset the Breakpoint up one line (that is, to the beginning of the
multiline code using the continuing (space/underscore). Then on Cell A1
again - Edit (F2) and the same code line code just set turned YELLOW
(indicating of course that it is the next line To BE RUN..) Placing my
cursor over the variable MinNum in the line before and also in the current
line --tooltip showed 2 (my correct minimum #).

In Cell G10 I entered =ISNUMBER(A2) and copied down 6 rows - ALL displayed
TRUE -- The values in A2:A8 are all numbers - constants (I think this is
proper
termonology)

In cell H4 if I enter =MIN(A2:A8) - 2 displays (Which is correct))

Wow, what else is there (to do)?
Appreciate your assistance,
Jim



If I understand you correctly, the first time through, the routine did NOT stop
at the End Function line. That means the routine exited prematurely on the
"Find" line.

For some reason, it seems that the FIND function is not working on your
machine. Very strange. And since I have XL2002, I can't replicate that.

Try this modification:

=============================
Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
Dim c As Range
MinNum = Application.WorksheetFunction.Min(rg)

For Each c In rg
If c.Value = MinNum Then
MinCellAddr = c.Address
Exit Function
End If
Next c
End Function
===============================

Using a For/Next loop will be slower than using the FIND function, but it
should work OK. You'd probably only notice the slow down on a very large
range.

I don't know why the FIND function isn't working. If the For/Next loop is too
slow, one thing you might try is to record a macro using the Find operation in
Excel, and, using the code that is recorded as a baseline, make the appropriate
modifications to use it in this routine.


--ron