Function to find the address of a cell

[Ron Rosenfeld]

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks

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
=====================================


--ron

 

[Ron Coderre]

If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT(A1))))

That will return the address of the cell that contains the lowest value.
Note 1: If there are duplicate minimum values, It will return the address of
it will return the location of the first.

Note 2: for simplicity sake, the referenced range cannot contain Blank
Cells. If the range may contain blanks, the formula becomes more cumbersome.

Does that help?

***********
Regards,
Ron

 

[Niek Otten]

Please fix your system date. It is frustrating the housekeeping in the
newsgroups.

 

[B. R.Ramachandran]

Ron,

A modification is needed to handle duplicate occurrence of the range-minimum.

I also thought of a similar formula first, but realized that the formula
won't work if there are duplicate minimum values; it will not return the
location of the first occurrence of the minimum; it would, on the other hand,
ADD all the row numbers of cells containing the minimum, and similarly add
the corresponding column numbers, and return an incorrect cell address as the
answer. For example, if B1 and B2 contain the range-minimum, the formula
will return "D3" which corresponds to ADDRESS(3,4).

Regards,
B. R. Ramachandran

 

[Ron Coderre]

Good point....and nice catch. Thanks!
My comment about the first min value was incorrect.

I think the point I was trying to make was this:
In it's most vanilla scenario (only one min value), the formula is pretty
darn long and complications would only make it more unwieldy.

Hmmmm....seems like I could have just said that in the first place, eh?

Anyway, since we're on the topic, here's the formula for finding the first
occurrence of the minimum value in a range referenced by text in Cell A1:

=ADDRESS(SUMPRODUCT(MIN(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1))+(((INDIRECT(A1))<>MIN(INDIRECT(A1)))*10^99))),SUMPRODUCT(MIN(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT(A1))+(((INDIRECT(A1))<>MIN(INDIRECT(A1)))*10^99))))

....and THAT only works as long as the minimum is not zero when there are
blank cells in the range (which would equate to zero).

***********
Regards,
Ron

 

[JMay]

Trying this -- I get #VALUE! as a result...
Any suggestions?
Tks..

 

[Ron Rosenfeld]

Trying this -- I get #VALUE! as a result...
Any suggestions?
Tks..

An error value in rg will cause that.







--ron

 

[Tom Ogilvy]

But a more likely cause is that Find doesn't work in a UDF used in a
worksheet in xl2000 and earlier.

--
Regards,
Tom Ogilvy

Trying this -- I get #VALUE! as a result...
Any suggestions?
Tks..

An error value in rg will cause that.

 

[Ron Rosenfeld]

But a more likely cause is that Find doesn't work in a UDF used in a
worksheet in xl2000 and earlier.

I've seen that written here before, but completely forgot about it.

We'll see what the OP has to say about his Excel version. Could always do a:

for each c in rg
test it
next c

which hopefully won't be too slow unless rg is huge.


--ron

 

[JMay]

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

 

[Ron Rosenfeld]

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

 

[Jim May]

Ron:
Thanks so much for the explanation. I tried everything you recommended,
(even the expanded code - This time on my AT-WORK PC - Orig is on my Home
PC);But all without success. Looks like time to
"we'll have to debug the code on your machine, to see
where it's going wrong" << I even (already) set the Breakpoints as you
suggest <<before End Function, but get no unusual results.
Hummmmm....
Tks,
Jim

 

[Ron Rosenfeld]

Ron:
Thanks so much for the explanation. I tried everything you recommended,
(even the expanded code - This time on my AT-WORK PC - Orig is on my Home
PC);But all without success. Looks like time to
"we'll have to debug the code on your machine, to see
where it's going wrong" << I even (already) set the Breakpoints as you
suggest <<before End Function, but get no unusual results.
Hummmmm....
Tks,
Jim

When you write "get no unusual results" do you mean that the code stopped at
each breakpoint, or not?

If it did stop at each breakpoint, after this line:

MinNum = Application.WorksheetFunction.Min(rg)

has been executed (in other words, when the code has stopped on the following
line), float your cursor over the MinNum and see what the value is.

If it says MinNum=0, then the problem is that the values in rg are text.

If you manually entered those values, then possibly the cells were formatted as
text before you entered your values (and changing the format will not change
this).

If the contents are the result of a formula, then we should look at the
formula.

If the contents were imported from an html document, web page, or some other
database, then we should determine what, exactly, is in there so as to clean it
up in the simplest fashion.


--ron

 

[Jim May]

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

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

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.

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)))

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(RNG,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)))

 

[Harlan Grove]

Ron Coderre wrote...

If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))
*ROW(INDIRECT(A1))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))
*COLUMN(INDIRECT(A1))))

....

FWIW, this only works when there's a single instance of the minimum
value.

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...
...

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(RNG,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

 

[Norman Jones]

Hi Ron,

[...]

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

This may be a red herring, but I note that Jim switched machines and
possibly, therefore OS.

[...]

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.

Harlan said:

which would precluse a 2-column array.

 

[Ron Rosenfeld]

which would precluse a 2-column array.

Ah. Now I see that 1D = 1 dimension.

thanks


--ron