Consulting

Results 1 to 2 of 2

Thread: Search Characters Within String

  1. #1
    VBAX Regular
    Joined
    Jan 2016
    Posts
    42
    Location

    Search Characters Within String

    I would like to have either formula or VBA that could perform the following:
    Search for the string from the list based on search parameters that are located at G2:K2. I need to search column A for all string combinations that contain characters typed in search boxes. For example, LZAAI the result should be AAZLI, LAAZI, LIAZA. If I erase letter I it should return ZAAL, AALZ or any combination that is on the list. So, the positions of the search characters donít matter as long as they are contained completely inside the codes from the list. I donít care if results are filtered out, highlighted or copied below search boxes, I just need to see these codes that match criteria. Also, anything that is greater than a number of searched characters should not be shown in the search results. If I erase one letter the results should not show codes greater than 4 characters etc. I am attaching workbook to make it more clear. I hope this is not too much of a headache. Thank you!
    Sample.xlsx

  2. #2
    VBAX Regular
    Joined
    Mar 2012
    Posts
    27
    Location
    Quote Originally Posted by barim View Post
    I would like to have either formula or VBA that could perform the following:
    Try this:

    Sub c64409c()
    'http://www.vbaexpress.com/forum/showthread.php?64409-Search-Characters-Within-String
    Dim r As Range
    Dim i As Long, j As Long, x As Long
    Dim va, vb
    Dim flag As Boolean
     
    Set r = Range("A1", Cells(Rows.count, "A").End(xlUp))
    va = r
    r.Interior.Color = xlNone
    vb = Range("G2:K2")
     
    For i = 1 To UBound(vb, 2)
    x = x + Len(vb(1, i))
    Next
     
    For i = 1 To UBound(va, 1)
        flag = True
     
        For j = 1 To UBound(vb, 2)
            If Len(vb(1, j)) = 1 Then
                If InStr(va(i, 1), vb(1, j)) = 0 Or Len(va(i, 1)) <> x Then
                    flag = False: Exit For
                End If
            End If
        Next
       
        If flag = True Then
        Cells(i, "A").Interior.Color = vbYellow
        End If
    Next
     
    End Sub

Posting Permissions

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