a formula that return the cell location in a range
 

Element value is in A4 and the range is A5:E30
B4 is to show the location

Element value may not be unique.
Thanks

a formula that return the cell location in a range
 

Homework?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"danpt" wrote in message
...
Element value is in A4 and the range is A5:E30
B4 is to show the location

Element value may not be unique.
Thanks

a formula that return the cell location in a range
 

Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function


and use in the worksheet like:


=WhereInTheWorld(A4,A5:E40)



--
Gary''s Student - gsnu200851


"danpt" wrote:

Element value is in A4 and the range is A5:E30
B4 is to show the location

Element value may not be unique.
Thanks

a formula that return the cell location in a range
 

Thank you very much.
It works great.


"Gary''s Student" wrote:

Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function


and use in the worksheet like:


=WhereInTheWorld(A4,A5:E40)



--
Gary''s Student - gsnu200851


"danpt" wrote:

Element value is in A4 and the range is A5:E30
B4 is to show the location

Element value may not be unique.
Thanks

a formula that return the cell location in a range
 

If the OP was willing to tolerate an error being returned if the search
fails (as opposed to your "No Luck" message), then a one-line UDF will
work...

Function WhereIs(rf As Range, r As Range) As String
WhereIs = r.Find(rf.Value, r(r.Count), , xlWhole, xlByRows).Address(0, 0)
End Function

Of course, if he didn't want the error, it would take a couple of extra
lines...

Function WhereIs(rf As Range, r As Range) As String
WhereIs = "No Luck"
On Error Resume Next
WhereIs = r.Find(rf.Value, r(r.Count), , xlWhole, xlByRows).Address(0, 0)
End Function

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function


and use in the worksheet like:


=WhereInTheWorld(A4,A5:E40)



--
Gary''s Student - gsnu200851


"danpt" wrote:

Element value is in A4 and the range is A5:E30
B4 is to show the location

Element value may not be unique.
Thanks

a formula that return the cell location in a range
 

Very nice....Thanks
--
Gary''s Student - gsnu200851


"Rick Rothstein" wrote:

If the OP was willing to tolerate an error being returned if the search
fails (as opposed to your "No Luck" message), then a one-line UDF will
work...

Function WhereIs(rf As Range, r As Range) As String
WhereIs = r.Find(rf.Value, r(r.Count), , xlWhole, xlByRows).Address(0, 0)
End Function

Of course, if he didn't want the error, it would take a couple of extra
lines...

Function WhereIs(rf As Range, r As Range) As String
WhereIs = "No Luck"
On Error Resume Next
WhereIs = r.Find(rf.Value, r(r.Count), , xlWhole, xlByRows).Address(0, 0)
End Function

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Try this small UDF:

Function WhereInTheWorld(rf As Range, r As Range) As String
Dim rr As Range
WhereInTheWorld = "No Luck"
v = rf.Value
For Each rr In r
If rr.Value = v Then
WhereInTheWorld = Replace(rr.Address, "$", "")
Exit Function
End If
Next
End Function


and use in the worksheet like:


=WhereInTheWorld(A4,A5:E40)



--
Gary''s Student - gsnu200851


"danpt" wrote:

Element value is in A4 and the range is A5:E30
B4 is to show the location

Element value may not be unique.
Thanks