help with VBA to find specific text within a string and within a specific range

[kbishop94]

I am trying to figure out how to have my code search within a specific range within a worksheet (and within in one column and down to the last VISIBLE row) for a specific string that may be contained within one or more of the cells within the stated range. I want it to work pretty much just like the available dialog box 'find' feature within excel for searching for a string somewhere in the worksheet:


the string to be searched will be entered by the user via a userform. The search for the specific string that is entered will be searched within two dates that are also entered via the userform (circled in red):



As you can see I already have lots of different criteria that is searchable, and everything works fine. I just am trying to add this feature to it as I have had some requests that it would be nice if it would be able to find ceratin keywords or phrases. The code for the search userform is rather long, but pretty simple in how it executes each search:

all the data that is to be searched is located on the main worksheet. each row represents an 'incident' that was previously entered.

first it grabs ALL the rows within the date range selected (shown above; circled in red) from the main sheet and copies them over to the sheet that is used to present the search results... (doesn't matter if the rows contain any matching search criteria was entered, it just grabs ALL the rows between the two dates.)

next it HIDES all of the rows that were copied and pasted onto this different worksheet (worksheet "REPORTS").

Then the code runs through each section that contains the code that represents each searchable section... (there are multiple sections for dropdown boxes on the form that contain choces, checkboxes for other choices and so on... the code is pretty much the same for each section... it first looks to see if that section was chosen or not (is the combobox empty or not? has the checkbox in the section been chosen or not?) If its empty (no choice) then it moves on to the next one... if not, then it looks for any matches within the rows that were previously hidden and if it finds one then it un-hides that row and then moves on to the next search criteria and so on...

The code that I am trying to come up with for a searchable string, however, will be different than the other sections of the code and Im struggling to get something to work... I believe using the InStr might work best, but maybe there is another way(?)

Ultimately I need the code to accomplish this:

look in the hidden rows within the worksheet and within a specific column (column H, starting at row 31 and down to the last row) and any cell in that row (RecordRow) change it to visible = True and keep all other non-matching rows hidden.

Here is one of the sections of code within the module. There are many more just like this but are unique to only to whatever searchable criteria was chosen on the userform... it rolls through each seaction and if nothing was selected for that section then it moves on, and if something WAS selected then it looks for any matches, and if it finds any matches, unhides that particular row and then moves on. The example code below is for one of the combox boxes:

'
' THIRD COMBOBOX: - "cboEmployees": SEARCHING FOR THE SPECIFIC PERSON WHO REPORTED THE INCIDENT!
'******************************************************************************************************************************
If cboEmployees.value = "" Or cboEmployees.value = "-" Then                   ' IF THIS COMBOBOX IS EMPTY, THEN SKIP THIS COMBOBOX ENTIRELY AND MOVE ON DOWN TO THE NEXT ONE:
    Else                                                                      ' ELSE COMBOBOX IS NOT EMPTY SO NOW RUN THE CODE TO SEE IF ANY RECORDS MATCH WHAT WAS SELECTED:
        DataCriteria3 = cboEmployees.value
    For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 10), Cells(rRow, 10)).SpecialCells(xlCellTypeVisible)
        If Cell.value = DataCriteria3 And Rows(Cell.Row).Hidden = False Then  ' IF THE COMBO SELECTION MATCHES ANY OF THE UNHIDDEN RECORDS, THEN THOSE ROWS *REMAIN* UNHIDDEN
                Rows(Cell.Row).Hidden = False
            Else                                                              ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE SELECTION, SO HIDE ALL OF THOSE ROWS
                Rows(Cell.Row).Hidden = True
            End If
        Next
' THE CODE BELOW LOOKS AT THE NUMBER OF ROWS THAT ARE NOW HIDDEN (WHICH INDICATE THAT THOSE ROWS DID *NOT* MATCH THE COMBOBOX SELECTION FOR A CUSTOMER) AND IF THAT NUMBER
' EQUALS 1 OR LESS, **AND** NO OTHER REMAINING COMBOBOXES HAVE CHOICES MADE IN THEM, THEN THE CODE WILL TELL THE USER THAT *NO RECORDS* WERE FOUND THAT MATCH HIS SEARCH
' CRITERIA AND WILL UNSORT THE MAIN WORKSHEET PAGE AND THEN RE-OPEN (RE-INIITIALIZE) THE 'frmReportCritieria' SO THAT A NEW SEARCH CAN BE PERFORMED:
Dim rEmp As Long
    rEmp = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
        If rEmp < 1 And cboLocation1.value = "" And cboEmployees.value = "" And cboIssuedTo.value = "" And chkCARyes.value = False Then
            MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
    With ws
        ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        ActiveSheet.Range(Cells(18, 1), Cells(rCol, 49)).AutoFilter Field:=2
        ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(rCol, 1), Cells(rCol, 1)).Select
    End With
'
Unload Me
frmReportCriteria.Show
'
Exit Sub
'
Else
End If
End If

Thanks for any help or suggestions to get it to do what I am trying to accomplish. Thanks again

Here is what the userform looks like and the options that can be selected by the user that are then used as search criteria:


and here are how the results are displayed on the sheet that you are directed to once the 'find all records' is clicked on the userform:


(FWIW, the column shaded gray with the label that says "Incident Description" is the column where I am wanting the string search to be performed and where it is to look for any possible matches. It is column 'H')

 

[kbishop94]

I got it working! as usual, I was over thinking it and I had most of the code already done and only needed to change something small from all the other similar events...

Here is what I ended up with that works:

'
' SEARCHABLE KEYWORD OR PHRASES:
'******************************************************************************************************************************
If txtKeyWord1.value = "" Or txtKeyWord1.value = "-" Then                               ' IF THE TEXTBOX FOR ENTERING A SPECIFIC KEYWORD OR PHRASE IS EMPTY,THEN SKIP THIS ONE AND MOVE ON TO THE NEXT EVENT:
    Else                                                                                ' ELSE IF IT IS NOT EMPTY THEN RUN THE CODE TO SEE IF ANY RECORDS MATCH THE SPECIFIC STRING THAT WAS ENTERED:
        DataCriteria15 = txtKeyWord1.value
    For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 8), Cells(rRow, 8)).SpecialCells(xlCellTypeVisible)
        If InStr(Cell.value, DataCriteria15) > 0 And Rows(Cell.Row).Hidden = False Then ' IF THE KEYWORD MATCHES EXACTLY WITHIN ANY OF THE TEXT (STRING) WITHIN ANY OF THE UNHIDDEN RECORDS WITHIN COLUMN "H", THEN THOSE ROWS *REMAIN* UNHIDDEN
                Rows(Cell.Row).Hidden = False
            Else                                                                        ' ELSE NO CURRENTLY UNHIDDEN RECORDS CONTAIN THE KEYWORD STRING, SO HIDE ALL OF THOSE ROWS
                Rows(Cell.Row).Hidden = True
            End If
        Next
' THE CODE BELOW WILL LOOK A THE NUMBER OF ROWS THAT ARE NOW HIDDEN (WHICH INDICATE THAT THOSE ROWS DID NOT MATCH THE COMBOBOX SELECTION FOR A CUSTOMER) AND IF THAT NUMBER
' EQUALS 1 OR LESS, **AND** NO OTHER REMAINING COMBOBOXES HAVE CHOICES MADE IN THEM, THEN THE CODE WILL STOP TELLING THE USER THAT NO RECORDS WERE FOUND AND WILL UNSORT THE
' MAIN PAGE AND THEN RE-OPEN (INIITIALIZE) THE frmReportCritieria SO THAT THE USER CAN TRY AGAIN:
Dim rKey As Long
    rKey = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
        If rKey < 1 And cboLocation1.value = "" And cboEmployees.value = "" And cboIssuedTo.value = "" And chkCARyes.value = False Then
            MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
    With ws
        ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        ActiveSheet.Range(Cells(18, 1), Cells(rCol, 49)).AutoFilter Field:=2
        ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(rCol, 1), Cells(rCol, 1)).Select
    End With
'
Unload Me
frmReportCriteria.Show
'
Exit Sub
'
Else
End If
End If
'

Specifically, this part of this event is really the only thing I needed to change from all the others in the module in order to get it to work:

        DataCriteria15 = txtKeyWord1.value
    For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 8), Cells(rRow, 8)).SpecialCells(xlCellTypeVisible)
        If InStr(Cell.value, DataCriteria15) > 0 And Rows(Cell.Row).Hidden = False Then ' IF THE KEYWORD MATCHES EXACTLY WITHIN ANY OF THE TEXT (STRING) WITHIN ANY OF THE UNHIDDEN RECORDS WITHIN COLUMN "H", THEN THOSE ROWS *REMAIN* UNHIDDEN
                Rows(Cell.Row).Hidden = False