Databases
StackOverflow2013
Postsdbo
POAND and OR function in VBA
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAND and OR function in VBA
    primarykey
    data
    text
    <p>is there a way to do a "AND" and "OR" function search? for example, for "AND" function, the user will type in two parameters, apple AND pear, this means that whatever row i am searching for, apple and pear must be in the same row. </p> <p>and for "OR" function, apple OR pear, means that either if the row has apple or pear, it will be shown in the new sheet this program has created.</p> <pre><code>Dim searchItems As Variant Dim searchString, searchString2 As String Dim y, z As Integer Dim data, data2 As String searchString = Application.InputBox("type in please") data2 = searchString data = searchString searchString2 = searchString 'split the search string on the word or searchString = InStr(searchString, " AND ") searchString2 = InStr(searchString2, " OR ") If searchString &gt; 0 Then data = Split(data, " AND ") searchItems = data ElseIf searchString2 &gt; 0 Then data = Split(data, " OR ") searchItems = data End If If IsEmpty(searchItems) Then MsgBox ("Please type ""and"" or ""or"" in capital Letters! ") Else For y = LBound(searchItems) To UBound(searchItems) Dim i As Long, nRowsAddePerSheet As Long, nRows As Long, _ nRowsMax As Long, nSheets As Long Dim strSearch2 Dim rg As Range, rgF As Range Dim wks Dim x, NumberOfWorksheet As Integer 'to count worksheet for loop 'strSearch2 = Replace(data, "*", "") If Len(data2) &lt;= 0 Then MsgBox "ABandon: Search string must not be empty." Exit Sub End If Application.ScreenUpdating = False nSheets = Sheets.Count nRowsMax = ActiveSheet.Rows.Count For x = 1 To nSheets On Error Resume Next Set wks = Worksheets(data2) If (Err) Then Set wks = Worksheets.Add(After:=Sheets(Sheets.Count)) wks.Name = data2 Err.Clear End If On Error GoTo 0 Set rg = Sheets("Sheet1").Cells(1).CurrentRegion Sheets(x).Activate Set rg = ActiveSheet.Cells(1).CurrentRegion nRows = rg.Rows.Count nRowsAddePerSheet = 0 For i = 1 To nRows Set rgF = rg.Rows(i).Find(searchItems(y), , xlValues, xlWhole) If Not rgF Is Nothing Then If (nRowsAddePerSheet &lt;= 0) Then If (i &lt;&gt; 1) Then rg.Rows(1).Copy wks.Range("A" &amp; nRowsMax).End(xlUp).Offset(1, 0) End If End If rg.Rows(i).Copy wks.Range("A" &amp; nRowsMax).End(xlUp).Offset(1, 0) nRowsAddePerSheet = nRowsAddePerSheet + 1 End If Next Next Set rgF = Nothing Set rg = Nothing Set wks = Nothing Application.ScreenUpdating = True Next y End If </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpnuts
    1. USuser2837847
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COYes it is possible. What problems are you facing?
      singulars
      1. POAND and OR function in VBA
      1. USSiddharth Rout
    2. CO@SiddharthRout whenever i'm done typing the searching parameters, the program creates multiple blank sheet, with only one or two sheet had the details i am looking for. Weird.
      singulars
      1. POAND and OR function in VBA
      1. USuser2837847
    3. COThat is because you are creating the sheets before the search happens `Set wks = Worksheets(data2)` What I would recommend if that if no match is found then delete the sheet.
      singulars
      1. POAND and OR function in VBA
      1. USSiddharth Rout
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload