Find a value in an array and return cell address

  • Thread starter Thread starter PJFry
  • Start date Start date
P

PJFry

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
 
If you are looking for a macro then use this
Sub findinList()
Dim c As Range, s As Long
Range("A1:Az100").Select
Set c = Selection.Find("Sprint")
Range("A1").Value= c.Address
End Sub
 
Try this array formula**:

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
If use this formula
="Column "&SUMPRODUCT(--(A1:AZ100="Sprint")*COLUMN(A1:AZ100))&" Row
"&SUMPRODUCT(--(A1:AZ100="Sprint")*ROW(A1:AZ100))

somewhere outside the range A1:AZ100 it will return something like
Column 4 Row 20
to tell you where the word is
best wishes
 
And this
=ADDRESS(SUMPRODUCT(--(A1:AZ100="Sprint")*ROW(A1:AZ100)),SUMPRODUCT(--(A1:AZ100="Sprint")*COLUMN(A1:AZ100)))
give the address
 
Sheeloo said:
If you are looking for a macro then use this
Sub findinList()
Dim c As Range, s As Long
Range("A1:Az100").Select
Set c = Selection.Find("Sprint")
Range("A1").Value= c.Address
End Sub
Click to expand...
....

Brilliant - NOT!

You're checking A1:AZ100, which means Sprint could be in cell A1, but
your macro would overwrite it in cell A1.

Fine example of a complete lack of practical experience.
 
Pl. refrain from personal comments on anyone's experience or lack thereof.

PJ wanted the result in A1, AND search in A1:Az100. If he puts the string in
A1...he will get the address which he wanted...

Code will work fine whether it is in A1 o not...

I give him enough credit to take this and adapt to his need.
 
You could have reduced your code to a one-liner...

Sub findinList()
Range("A1").Value = Range("A1:Az100").Find("Sprint").Address
End Sub

I would point out that both your code and the above code will error out if
the word Sprint doesn't appear in the grid, so perhaps an On Error GoTo trap
might be a good thing to add.
 
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
 
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
 
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
 
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
 
Hi all,
I need a formula to put id from column C text value in Column match col B.

Col A is the DATA (TO BE SERACHED).
Col B is LABEL to match data
Col C has the labelid number for the labels in col B.
COl D is where I want to place ID.

How do I do this with a formula? It is big table and has many entries).

(Colum D is result I need)

-Nitin

A B C D
DATA LabeL LabelID# RESULT
a a 1 1
c b 88 89
b c 89 88
n d 3 67
m e 7 N/A
k gg 8 999
ll ll 9 9
a k 999 1
n N 67 67
p ?
p ?
a ?
c .... (formula..)
 
Please explain in more detail what you you need.

The post to which you replied states..................

Assumes you already know the cell content which is "Sprint"


Gord Dibben MS Excel MVP
 
Back
Top