Search data in 4 Contiguous cells within a selected area

[binar]

Fellow Forum Members,
How can a get Excel 2013 to search 4 numbers in 4 contiguous cells within a range I select? For example in the graphic below I want Excel to SEARCH for the numbers 4,11,33,40 (in that order) which is located on Row6. In addition, the SEARCH needs to only be performed within a range I select which in the example below is the area colored in YELLOW.

Table.jpg

I'm not a VBA expert, but my guess is such a search can be done using a VBA script. I very much hope someone out there can post the VBA script that can perform such a search because I don't have the coding skills to create such a VBA script from scratch. In addition, if there is a way to perform such a complex search using a formula I would very much appreciate getting such a formula posted in this thread. Thanks in advance for any help.

[Leith Ross]

Hello binar,

Is the search only by rows (horizontally)?

[binar]

Hello binar,

Is the search only by rows (horizontally)?

Leith, thanks for your post. I can understand how my use of the word "Row" can confuse things. Below is a description of the customized search I'm seeking for Excel 2013 to make written in the most logical way possible I can think of:


"In the seclected region of cells find the 4 horizontal contiguous cells that contain numbers matching 4,11,33,40."

I'm surprised Excel does not offer such a search capability right out of the box because not all searches necessarily target data contained in a single cell. Sometimes one needs to be able to search a series of contiguous data like I'm trying to do. If you or anyone out there can post a solution to this problem it will be greatly appreciated. Thanks again.

[Kenneth Hobs]

I am not sure what your goal is. How are the numbers determined or is it just a static search for just those 4? If found, then what, return the row number or select the range? What if more than one match exists, is the first found sufficient? What if no match is found?

Obviously, if you had a concatenation helper column, such things are easier. I have seen some use that approach but 1 in each cell is 1111 while 11 in column A and 11 in column B and empty in other two is 1111 too. I would add a vbTab delimiter.

In code, one could do 4 finds and use Intersect(). It is not that difficult. Of course if any find's return range is nothing, then no match was found.

Another way would be iterate column A or the rows in the selection and use a delimiter like vbTab and see if it matches the vbTab delimited input string. I had thought that using the Forms object might make the vbTab delimiter via a clipboard doable.

So, several ways to do it. I guess the 4 find method might be the best but then I'm not great with built-in formula methods.

Sub Main()  
  Dim f1 As Range, f2 As Range, f3 As Range, f4 As Range, f As Range, c As Range
  Dim i1%, i2%, i3%, i4%
  i1 = 4: i2 = 11: i3 = 33: i4 = 40
  
  On Error GoTo endSub
  
  With Selection
    'www.cpearson.com/Excel/FindAll.aspx
    Set f1 = FindAll(.Columns(1), i1).EntireRow
    Set f2 = FindAll(.Columns(2), i2).EntireRow
    Set f3 = FindAll(.Columns(3), i3).EntireRow
    Set f4 = FindAll(.Columns(4), i4).EntireRow
    Set f = Intersect(f1, f2, f3, f4, Selection)
    'f.Select  'Selects all
    f.Rows(1).Select 'Select only first found
  End With
  
endSub:
End Sub

[Leith Ross]

Hello Binar,

This VBA macro is a UDF. You can use it like a Formula on the worksheet. It will return the address of the first cell in the match. In this case, it would return $A$6. If there is no match then an empty string is returned "".

Example

=Search4($A$1:$D$10,4,11,33,40)

Copy this code into a new VBA Module in your workbook.

' Written: December 07, 2016
' Author:  Leith Ross


Function Search4(ByRef Rng As Range, ParamArray SearchTerms() As Variant) As String


    Dim Data As Variant
    
        If UBound(SearchTerms) <> 3 Then Exit Function
        
        Data = Rng.Value
        
        For j = 1 To Rng.Rows.Count
            If Data(j, 1) = SearchTerms(0) Then
                If Data(j, 2) = SearchTerms(1) Then
                    If Data(j, 3) = SearchTerms(2) Then
                        If Data(j, 4) = SearchTerms(3) Then
                            Search4 = Cells(j, Rng.Column).Address
                            Exit Function
                        End If
                    End If
                End If
            End If
        Next j
            
End Function