How to search text string within range VBA

[Fractalis]

Hello to all,

I want to search the substring within a range that is not in a single row and if match select the cells where is the string present.


I was able to make a code that works but only if the data is in a single row, but I don't know how to do it if the data is in a range with several rows and columns.


I generate the data with "=DEC2HEX(RANDBETWEEN(0,255),2)" in A1 and then copy across and down in range A1:T10.


For example the data in Range A1:T10 is like this:

   A     B     C     D     E     F     G     H     I     J     K     L     M     N     O     P     Q     R     S     T 
1  F2    37    C6    7D    DB    00    8E    CB    23    A4    97    B8    20    92    94    69    CC    E7    EF    DA
2  81    10    BC    DA    AD    8A    D3    04    86    35    0E    E9    B7    D5    47    2F    8B    90    B6    3C
3  2B    88    00    A9    D0    [B][COLOR=#ff0000]D1    26    45    43    F1    9E    56    86    0C    8A    E1    7F    E1    26    C5
[/COLOR][/B]4[B][COLOR=#ff0000]  33    3C    9D    3C    39    DD    E5    4C    08    5D    8D    85    2C    96    F3    64    0D    CB    5F    B6
[/COLOR][/B]5[B][COLOR=#ff0000]  79    14    81    DB    89    47    FA    83    84    0D    F5    0E[/COLOR][/B]    D9    5F    19    72    5D    1B    66    EC
6  23    BC    F5    59    D4    43    E3    CC    10    EA    07    C3    63    69    3B    B9    63    2B    52    91
7  61    A7    2E    84    56    E4    F1    D4    86    49    C3    A2    27    60    90    93    80    6D    84    F9
8  1B    93    4D    55    37    A9    8B    27    CF    3F    A9    B8    7C    46    4D    1B    6F    CA    7A    C2
9  2A    67    22    A6    B0    D3    5B    5E    81    AA    04    29    40    19    9B    F0    86    22    27    D7
10 1E    13    E1    3E    37    F6    7E    4E    EB    21    C6    7E    A3    70    3F    59    36    C5    4A    BF

and I want to search within Range A1:T10 the following string


"D1264543F19E56860C8AE17FE126C5333C9D3C39DDE54C085D8D852C96F3640DCB5FB6791481DB8947FA83840DF50E"

The string is present and is shown in red within the range.

Maybe somebody could help me with this problem.

Thanks in advance.

 

[Eric W]

If you have the CONCAT function from Excel 365, it's pretty easy. Given the layout you showed, and if the search string is in A12, then try this formula:

=IFERROR(ADDRESS(FIND(A12,CONCAT(A1:T10))/40+1,MOD(FIND(A12,CONCAT(A1:T10))/2,20)+1),"")


If you don't have CONCAT, then it's much tougher. Or are you looking for a VBA solution, like the thread title suggests?

 

[rlv01]

With the older CONCATENATE function you could create a helper column using a formula like this

=CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1)

and search that.

 

[Fractalis]

Hi guys, thanks for answer.

I don't have CONCAT only CONCATENATE but what I need is tolook for the given string within the range and if match select the cells where is the string present

In this case the string is present in range F3:L5. Then I need F3:L5 be selected to see where is the string given inside the Range A1:T10.I think to select the string across different cells only is possible with VBA.

 

[Eric W]

Try:

Sub temp1()
Dim c As Range, x As Long, y As Long, z As Long, r As Range


    For Each c In Range("A1:T10")
        str1 = str1 & c.Value
    Next c
    x = InStr(str1, Range("A12").Value)
    If x > 0 Then
        x = (x - 1) / 2
        y = Len(Range("A12").Value) / 2
        z = 0
        Set r = Nothing
        For Each c In Range("A1:T10")
            If z >= x And z < x + y Then
                If r Is Nothing Then
                    Set r = c
                Else
                    Set r = Union(r, c)
                End If
            End If
            z = z + 1
        Next c
        r.Select
    End If
        
End Sub

 

[Fractalis]

Hi Eric,

Thanks for the help. It seems to work pretty fine in many cases, but I'm not sure why fails when the string to search is larger, for example if string is 2,356 characters in length only selects less than 200 cells and should select a range of 1178 cells.

I tried with a range bigger from C3 to AP477 and a string to search of length of 2356. What could be the issue?

I've modified your code like this:

Sub temp1()
Dim c As Range, x As Long, y As Long, z As Long, r As Range
Dim searchStr As String


searchStr = InputBox("Write string to search", "Search string")
searchStr = UCase(searchStr)
    For Each c In Range("C3:AP477")
        str1 = str1 & c.Value
    Next c
    x = InStr(str1, searchStr)
    If x > 0 Then
        x = (x - 1) / 2
        y = Len(searchStr) / 2
        z = 0
        Set r = Nothing
        For Each c In Range("C3:AP477")
            If z >= x And z < x + y Then
                If r Is Nothing Then
                    Set r = c
                Else
                    Set r = Union(r, c)
                End If
            End If
            z = z + 1
        Next c
        r.Select
    End If
        
End Sub

 

[Eric W]

A couple of thoughts. First, are all the values 2-digits? If not, the number of ranges to add would not be correct. That would not be too hard to correct for.

Sub temp1()
Dim c As Range, x As Long, y As Long, z As Long, r As Range, str1 As String
Dim searchStr As String


    searchStr = UCase(InputBox("Write string to search", "Search string"))


    For Each c In Range("C3:AP477")
        str1 = str1 & c.Value
    Next c
    x = InStr(str1, searchStr)
    If x > 0 Then
        y = Len(searchStr)
        z = 1
        Set r = Nothing
        For Each c In Range("C3:AP477")
            If z >= x Then
                If r Is Nothing Then
                    Set r = c
                Else
                    Set r = Union(r, c)
                End If
                
            End If
            z = z + Len(CStr(c))
            Debug.Print x, y, z
            If z >= x + y - 1 Then Exit For
        Next c
        r.Select
    End If
        
End Sub

I've also seen some indications that there may be a limit to the number of ranges that can be included in a Union, but nothing definite. I'd think you're not near that limit if there is one. If you are, I can think of a complicated way around it, but hopefully the previous macro will resolve the issue.

 

[Rick Rothstein]

I generate the data with "=DEC2HEX(RANDBETWEEN(0,255),2)" in A1 and then copy across and down in range A1:T10.

Do you leave this formula in the cells permanently or do you Copy/PasteSpecial values over top of the formulas to convert them to constants?

 

[Fractalis]

A couple of thoughts. First, are all the values 2-digits? If not, the number of ranges to add would not be correct. That would not be too hard to correct for.

Sub temp1()
Dim c As Range, x As Long, y As Long, z As Long, r As Range, str1 As String
Dim searchStr As String


    searchStr = UCase(InputBox("Write string to search", "Search string"))


    For Each c In Range("C3:AP477")
        str1 = str1 & c.Value
    Next c
    x = InStr(str1, searchStr)
    If x > 0 Then
        y = Len(searchStr)
        z = 1
        Set r = Nothing
        For Each c In Range("C3:AP477")
            If z >= x Then
                If r Is Nothing Then
                    Set r = c
                Else
                    Set r = Union(r, c)
                End If
                
            End If
            z = z + Len(CStr(c))
            Debug.Print x, y, z
            If z >= x + y - 1 Then Exit For
        Next c
        r.Select
    End If
        
End Sub

I've also seen some indications that there may be a limit to the number of ranges that can be included in a Union, but nothing definite. I'd think you're not near that limit if there is one. If you are, I can think of a complicated way around it, but hopefully the previous macro will resolve the issue.

Hi Eric, I think I found the issue.

I inserted inputbox to pass to a variable to make easy for user, but inputbox it seems doesn't allow more than 200 characters, the string to search when I paste it in inputbox is cut and isntead to look for a string of 2000 characters always look for a string with a length of 200 o so.

Is there a way to use inputbox to allow more than 2000 characters?

 

[Fractalis]

Do you leave this formula in the cells permanently or do you Copy/PasteSpecial values over top of the formulas to convert them to constants?

Hi Rick,

I use the formula mentioned to generate the data and then Copy/PasteSpecial values over top of the formulas to convert them to constants.