Find a value in an array and return cell address
 

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

Find a value in an array and return cell address
 

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


"PJFry" wrote:

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

Find a value in an array and return cell address
 

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

Find a value in an array and return cell address
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

Find a value in an array and return cell address
 

And this
=ADDRESS(SUMPRODUCT(--(A1:AZ100="Sprint")*ROW(A1:AZ100)),SUMPRODUCT(--(A1:AZ100="Sprint")*COLUMN(A1:AZ100)))
give the address
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

Find a value in an array and return cell address
 

Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com"
wrote...
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
....

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.

Find a value in an array and return cell address
 

All solid answers. I went with the one below and it did the trick perfectly.

Thanks!
PJ

"Bernard Liengme" wrote:

And this
=ADDRESS(SUMPRODUCT(--(A1:AZ100="Sprint")*ROW(A1:AZ100)),SUMPRODUCT(--(A1:AZ100="Sprint")*COLUMN(A1:AZ100)))
give the address
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

Find a value in an array and return cell address
 

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.


"Harlan Grove" wrote:

Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com"
wrote...
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
....

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.

Find a value in an array and return cell address
 

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.

--
Rick (MVP - Excel)


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
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


"PJFry" wrote:

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

Find a value in an array and return cell address
 

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

Find a value in an array and return cell address
 

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

Find a value in an array and return cell address
 

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

Find a value in an array and return cell address
 

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