Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Find text string in an ARRAY of cells

    I have a text string in, say, A1. I want to look at,say, B1:G500 and find out the cell address where A1 is found in that array.
    I've been fooling around with array formula variations of match, search, etc. and can't seem to nail it.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Just what do you want to do with the results?

    This will have to be VBA as an Excel formula won't return multiple cell addresses only the results of calculating those addresses.

    More details please?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Code:
    Public Sub FindString()
    Dim rng As Range, cell As Range
    Set rng = Range("B1:G500")
    For Each cell In rng
        x = InStr(1, cell, Range("A1"), vbTextCompare)
        If x > 0 Then
            Message = Message & cell.Address & " "
        End If
    Next cell
    If Message = "" Then
        MsgBox "There are no matches found"
    Else:
        MsgBox Message
    End If
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maudibe almost has what I need. The specifics are: In column AC (starting in row 2; row 1 has column header) I have text strings (they're all the same length, but I suspect that doesn't matter). In B2:M1000, I have a sparse matrix of cells (i.e., many blank cells but many with text strings I'm trying to search). In column BT (starting in row 2; row 1 has column header) I want the cell address from the matrix range where the respective string was found in the matrix.

    So, if AC2 is found in C80, I'd like C80 in BT2, etc.

    Ultimately, I'm using that reference (C80, for example) to grab other data for other processing.

    Would be nice if a future release of Excel allows you to search a matrix and give the position of the searched-for value even if that's the numeric position of an unraveled matrix (turn the matrix into a vector string, so a 10 x 5 would be a 50-long string) -- then some math could create the row, column value.

    Hope that's clear(er).

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Code:
    Public Sub FindString()
    Dim Rng As Range, CritCell As Range, RngCell As Range, CritRng As Range
    Set Rng = Range("B2:M1000")
    Lastrow = ActiveSheet.Cells(Rows.Count, "AC").End(xlUp).Row
    Set CritRng = Range("AC2:AC" & Lastrow)
    For Each CritCell In CritRng 'CRITERIA COLUMN
        For Each RngCell In Rng 'MATRIX
            x = InStr(1, RngCell, Range("AC" & CritCell.Row), vbTextCompare)
            If x > 0 Then
                Message = Message & RngCell.Address & " "
            End If
        Next RngCell
        If Message = "" Then
            Cells(CritCell.Row, "BT") = "No Matches"
        Else:
            Cells(CritCell.Row, "BT") = Message
        End If
        Message = ""
    Next CritCell
    End Sub

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    kweaver (2016-07-30)

  7. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    GREAT for 239 rows, then I ended up interrupting the macro after about 4 minutes of additional results. Was I too impatient?
    My AC column has 683 rows in it (incl header row 1). The current "matrix" runs to column M but currently has (only) 131 rows (will eventually have more). When I changed the upper bound of the matrix to 131, it still cranked quite a while and I cancelled it.

    I'll let it run later and see what happens unless you have some other insight.

    Thanks Maudibe

    Follow-up: Patience is a virtue...my mother always said that. TRUE. Thanks!
    Last edited by kweaver; 2016-07-30 at 15:33.

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Have you tried turning calcs to manual before running Maud's excellent routine?
    ..and make sure the VBA editor is NOT open when you run the macro.
    ..that should speed things up a bit.

    If you still need it faster than that, post back here.

    zeddy

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    This should be faster

    Code:
    Public Sub FindString()
    Dim Rng As Range, CritCell As Range, CritRng As Range
    Set Rng = Range("B2:M1000")
    Lastrow = ActiveSheet.Cells(Rows.Count, "AC").End(xlUp).Row
    Set CritRng = Range("AC2:AC" & Lastrow)
    For Each CritCell In CritRng 'CRITERIA COLUMN
        With ActiveSheet.Range("B2:M1000")
            Set Rng = .Find(What:=CritCell, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not Rng Is Nothing Then
                Cells(CritCell.Row, "BT") = Rng.Address
            Else
                Cells(CritCell.Row, "BT") = "No Matches"
            End If
        End With
    Next CritCell
    End Sub
    Last edited by Maudibe; 2016-07-30 at 16:48.

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Your first code would find multiple cells containing the relevant text.
    You second method will only report the first matching cell address.

    So, kweaver, do you have multiple occurrences (i.e. in different cells), or not???

    zeddy

  11. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maudibe's first routine uncovered a few multiples which I didn't think existed. GREAT to learn that. Bad data from the client, obviously. I'll try the 2nd also. I like them both...thanks again, Maudibe!!!

    Zeddy: I didn't have the editor open but I did have the calcs at automatic.

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The following code will check for multiple instances using the faster find method

    In a standard module
    Code:
    Public Sub FindString()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim CritCell As Range, CritRng As Range, Keyword As Range
    Lastrow = ActiveSheet.Cells(Rows.Count, "AC").End(xlUp).Row
    Set CritRng = Range("AC2:AC" & Lastrow) 'SET CRITERIA RANGE
    '================================
    'FIND MATCH IN SEARCH RANGE
    For Each CritCell In CritRng 'CRITERIA COLUMN
        With ActiveSheet.Range("B2:M1000") 'SEARCH RANGE
    '--------------------------------
    'CHECK FOR FIRST INSTANCE
            Set Keyword = .Find(What:=CritCell, after:=.Cells(.Cells.Count), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Keyword Is Nothing Then 'NO INSTANCES FOUND
                Cells(CritCell.Row, "BT") = "No Matches"
                GoTo nextcrit
            Else: 'INSTANCE FOUND
                NextKeyword = Keyword.Address 'REMEMBER FIRST INSTANCE ADDRESS
                Cells(CritCell.Row, "BT") = Keyword.Address
            End If
    '--------------------------------
    'CHECK FOR SUNSEQUENT INSTANCES
        Do
            Set Keyword = .FindNext(after:=Keyword)
            If NextKeyword <> Keyword.Address Then 'CHECK IF REPEATING
                Cells(CritCell.Row, "BT") = Cells(CritCell.Row, "BT") & " " & Keyword.Address
            End If
        Loop While Not Keyword Is Nothing And Keyword.Address <> NextKeyword 'CHECK FOR REPEAT
        End With
    '================================
    nextcrit:
    Next CritCell
    '--------------------------------
    'CLEANUP
    Set Keyword = Nothing
    Set CritCell = Nothing
    Set CritRng = Nothing
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •