Stefi

The "best" solution is one that works across all versions, not just Will's
version.


Gord Dibben MS Excel MVP

On Wed, 4 Apr 2007 07:10:03 -0700, Stefi
wrote:

Yes, it's a nice solution. The best thing should have been to ask Will what
version does he use!

Stefi


„Bob Phillips” ezt írta:

Or change the UDF

Public Function FindInRng(findrng As Range, ansrng As Range) As String
Dim oRow As Range
Dim iPos As Long
For Each oRow In findrng.Rows
On Error Resume Next
iPos = Application.Match(ansrng, oRow.Cells, 0)
On Error GoTo 0
If iPos 0 Then
FindInRng = Application.Substitute( _
Cells(oRow.Row, iPos).Address(, False), "$", ",")
Exit For
Else
findrng = ""
End If
Next oRow
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stefi" wrote in message
...
Then it's up to Will either to use XL2003 or find somebody who can solve
this
task with worksheet functions!
Stefi


"Dave Peterson" ezt írta:

If that UDF is gonna be used in a cell on a worksheet, then it won't work
until
xl2002.

..Find doesn't work in xl2k and below if used in a function called from a
cell on
a worksheet.

Stefi wrote:

That's another case!
I could solve it only with a UDF:

Public Function FindInRng(findrng As Range, ansrng As Range) As String
FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1),
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address(False, False)
End Function

Usage (answer being in G1):

=findinrng(A1:F2,$G$1)

Adjust A1:F2 range to your needs!

Regards,
Stefi

?zWill?? ezt Ã*rta:

stefi,

wat if i have a few rolls of number?

A B C D E F
1 5 6 7 8 9 10
2 11 12 13 19 20 21

i wan my ans to show; number 8 belong to D,1

I try to use ur formula but I cant get the result

"Stefi" wrote:

If your answer is in A2, then
=ADDRESS(1,MATCH($A$2,A1:F1,0),4,1)

gives C1.

Regards,
Stefi

?zWill?? ezt Ã*rta:

Hi,

Can excel search which cells my answers belong to?

example:
A B C D E F
4 2 3 6 7 9

Lets say my ans is "3"

it will show "number 3 belong to (C,1)"

--

Dave Peterson