VBA - How to exclude rows from search results if a cell is = to a specific value

KadeFoster · Oct 5th 2021

Hey all,

I am wanting to exclude certain results from my search if one of the cells value is Completed and Cancelled.

I have managed to find some code that loops through my list looking for rows with a certain value, i just don't know how to make it exclude the rows with the above values.

Status1 & Status2 are the values i am trying to ignore. They are located in column 15 on the "DataBaseTasks" sheet.

Below is my code:

Code

'********** SETS UP THE SOURCE FOR THE ENTERPRISE LIST BOX WITH ENTERPRISE TASKS **********************************************************************
Sub SetupEntLstBox()

    Dim RowNum As Long
    Dim SearchRow As Long
    Dim EntMine As String
    Dim Status1 As String
    Dim Status2 As String

    RowNum = 2
    SearchRow = 2
    EntMine = "Enterprise"
    Status1 = "Completed"
    Status2 = "Cancelled"
    
    Worksheets("DataBaseTasks").Activate
    
    Do Until Cells(RowNum, 1).Value = ""
             
        If InStr(1, Cells(RowNum, 3).Value, EntMine, vbBinaryCompare) > 0 Then
            Worksheets("EnterpriseTasks").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 4).Value = Cells(RowNum, 4).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 5).Value = Cells(RowNum, 5).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 6).Value = Cells(RowNum, 6).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 7).Value = Cells(RowNum, 7).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 8).Value = Cells(RowNum, 8).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 9).Value = Cells(RowNum, 9).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 10).Value = Cells(RowNum, 10).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 11).Value = Cells(RowNum, 11).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 12).Value = Cells(RowNum, 12).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 13).Value = Cells(RowNum, 13).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 14).Value = Cells(RowNum, 14).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 15).Value = Cells(RowNum, 15).Value
            SearchRow = SearchRow + 1
        End If
        RowNum = RowNum + 1
    Loop
    
    If SearchRow = 2 Then
        Exit Sub
    End If
End Sub

Display More

**royUK** · Oct 5th 2021

Attach an example workbook and explain what the code is supposed to be doing.

KadeFoster · Oct 6th 2021

Ok have attached my project. This is my first project in VBA, so i have been learning as i go along.

The above code is located on the CheckScaleForm

It is used to search DataBaseTasks sheet to create a list of Tasks related to the specific mine on separate sheets. I want the code to only show tasks that aren't Cancelled or Completed in these list boxes.

Those sheets then are used for the 3 of my list boxes to display the tasks for that mine.

You can then double click an a task and write a record for it and save it. Depending on your choices it may generate an email.

There is an error popping up to but i will ask for help about that after this is sorted. One problem at a time. I have managed to get this far on my own. My code may not be pretty but it mostly works.

VBA - How to exclude rows from search results if a cell is = to a specific value

Participate now!

Share