Philip,

Try this version:

Function FindMe(fVal As Variant, _
fRng As Range) As String

Dim myC As Range

FindMe = ""
For Each myC In fRng
If myC.Value = fVal Then
If FindMe = "" Then
FindMe = myC.Address
Else
FindMe = FindMe & ", " & myC.Address
End If
End If
Next myC

If FindMe = "" Then FindMe = "None Found"

End Function


Used like

=FINDME(5,A10:D25)
=FINDME(A1,A10:D25)

HTH,
Bernie
MS Excel MVP



"Philip Mark Hunt" wrote in
message ...
Dear Bernie

The function looks to do just what I want but I need it to be searching
for
a number, not a string. Please tell me how I edit it. I can se the logic
but
I'm just not full bottle on the correct syntax for VBA yet - just at the
beginning of my learning for the website.

I look forward to your reply. You're being a great mate, Thanks very
much.

Best regards

Philip
--
Graewood Business Services, Kwinana, Perth, Western Australia


"Bernie Deitrick" wrote:

Philip,

You could use a UDF:

Function FindMe(fStr As String, _
fRng As Range, _
Optional boolMC As Boolean) As String

Dim myC As Range

FindMe = ""
For Each myC In fRng
If IIf(boolMC, myC.Value, UCase(myC.Value)) = _
IIf(boolMC, fStr, UCase(fStr)) Then
If FindMe = "" Then
FindMe = myC.Address
Else
FindMe = FindMe & ", " & myC.Address
End If
End If
Next myC

If FindMe = "" Then FindMe = "None Found"

End Function


Used like this to match case:
=FindME("sprint",A1:AZ100,FALSE)

and like this to ignore case:
=FindME("sprint",A1:AZ100,TRUE)


HTH,
Bernie
MS Excel MVP


"Philip Mark Hunt" wrote in
message
...
Dear Biff

Could you please offer a variation of your formula, which would cope
with a
situation where there is more than one occurence of the search value.
I want
the interim array formula result to be a list of addresses, e.g.
"E356,AY784,
AY905". which I can then put through Longre's MCONCAT.

Best regards

Philip Hunt
--
Graewood Business Services, Kwinana, Perth, Western Australia


"T. Valko" wrote:

Try this array formula**:

=ADDRESS(MAX((A1:AZ100="sprint")*ROW(A1:AZ100)),MA X((A1:AZ100="sprint")*COLUMN(A1:AZ100)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"PJFry" wrote in message
...
I need to be able to find a text value in an array and have it return
the
cell address.

For example, I would need for find 'Sprint' somewhere in A1:AZ100.
Some
months it could be in A1, others it cound be in AA90. It would only
appear
once. If it appears in cell A1 I want the result of the formula to
be A1.

Can this be done?

Thanks!
PJ