Multiple search criteria plus AND/OR - going around in circles

SimoninParis

New Member
Joined
Mar 2, 2012
Messages
25
Hello out there,

Can anybody help me?

I am building an Excel workbook in which the main data worksheet has 47 fields. The first field is a unique numerical reference number, the last is a date stored as an integer. The remainder are either string or byte (derived from true/false from a userform). I need to be able to construct a search subroutine to return records matching up to 22 criteria from a "Search Userform". In addition, the subroutine needs to be able to identify one field which may contain " "xxx" AND/OR "yyy" AND/OR "zzz" ".

Do I need a search array? Do I need MATCH and/or Instr?

My VBA powers have met their match and been found wanting.

Any help, advice and/or guidance would be gratefully received.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
When you are working with VBA, then things get pretty simple. In most cases forget about Excel sheet formulas.
In this case, because you do a lot of reads (all these cells need to be checked) it may be more efficient to work with an array.

In the example below I have lot's of comments to guide you through the coding

VBA Code:
Sub AndOr()
    Dim vInp As Variant, vOut As Variant
    Dim lR As Long, lOut As Long, lID As Long
    
    'Read the sheet into the array:
    vInp = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    
    'vInp is now an array with the rows as the first parameter and the columns as the 2nd, so: _
     vInp(3,5) is the same as cell E3 _
     vInp(12,27) is the same as cell AA12
    
    'column A holds the ID numbers - vInp(x,1)
    
    ReDim vOut(1 To 1, 1 To 1)
    lC = 1  'counter for the output array rows
    
    
    'if something is true there are two Identical ways of testing:
    '   If x = True Then
    'or
    '   If x Then
    
    'similar to test if something is false
    '   If x = False Then
    'or
    '   If not x Then
    
    'in both cases the second option is slightly faster. In loops that makes a difference
    
    
    'Loop through the rows using lR as counter
    'Yo don't know the size of the array, UBound(_) will tell you. _
    UBound(array,1) = number of rows _
    UBound(array,2) = number of columns
    
    'let's assume the input of columns C,D, G, AA, AC need to be true
    'and E, J, Z need to be false
    'and K and L need to be both True, or both False
    'and B needs to be "Female"
    
    
    'Assuming there is a header row the counting starts at 2
    For lR = 2 To UBound(vInp, 1)
        If vInp(lR, 3) And vInp(lR, 4) And vInp(lR, 7) And vInp(lR, 27) And vInp(lR, 29) And _
           Not vInp(lR, 5) And Not vInp(lR, 10) And Not vInp(lR, 26) And _
           (vInp(lR, 11) = True And vInp(lR, 12) = True) Or (vInp(lR, 11) = False And vInp(lR, 12) = False) And _
           vInp(lR, 2) = "Female" Then
            'all the fields are correct, add the ID number to the output array
                vOut(lC, 1) = vInp(lR, 1)
                lC = lC + 1 'increment the counter
                ReDim Preserve vOut(1 To lC, 1 To 1)
        End If
    Next lR
    
    'All's processed. Output the output array to another sheet
    Sheets("Sheet2").Range("A2").Resize(UBound(vOut, 1), 1).Value = vOut
    
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top