| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP & RETURN CELL ADDRESS | Excel Worksheet Functions | |||
| Find Max in array- return corresponding cell | Excel Worksheet Functions | |||
| Return Cell Address of MIN value - array formula | Excel Worksheet Functions | |||
| Array Formula to find Average Return | Excel Discussion (Misc queries) | |||
| Find max number of character and return cell address | Excel Worksheet Functions | |||
Linear Mode
