Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· OpenAIAi2 WildChat-4.8M (AI2)

In the following VBA, I need to change the search condition of 'nameToFind =…

  1. User

    In the following VBA, I need to change the search condition of 'nameToFind = personResponsibleSheet.Range("A2").Value' in sheets range 'If nameToFind = "" Or currentSheet.Cells(i, "AB").Value = nameToFind Then' What I would like is as follows: If any of the words in personResponsibleSheet.Range("A2").Value are found in the sheet range AB, then provide the data requested that will be dispalyed in column B & C of sheet Person Responsible.

    Sub RunSearch()

    Me.Unprotect Password:="edit"
    Application.EnableEvents = False
    
    Dim personResponsibleSheet As Worksheet
    Set personResponsibleSheet = ThisWorkbook.Worksheets("Person Responsible")
    

    ' Clear existing entries in columns B and C, starting from row 2 personResponsibleSheet.Range("B2:C" & personResponsibleSheet.Cells(Rows.Count, "B").End(xlUp).Row).ClearContents

    Dim nameToFind As String
    nameToFind = personResponsibleSheet.Range("A2").Value
    
    Dim sheetToCheck As String
    sheetToCheck = personResponsibleSheet.Range("A5").Value
    
    Dim checkSheets As Collection
    If sheetToCheck = "All Checks" Then
        Set checkSheets = GetCheckSheets ' Search all "Checks" sheets
    ElseIf sheetToCheck <> "" Then
        Set checkSheets = New Collection
        If WorksheetExists(sheetToCheck) Then
            checkSheets.Add ThisWorkbook.Worksheets(sheetToCheck)
        End If
    ElseIf nameToFind <> "" Then
        Set checkSheets = GetCheckSheets ' Search all "Checks" sheets if only name is provided
    Else
        ' No name in A2, no sheet in A5, check all "Checks" sheets
        Set checkSheets = GetCheckSheets
    End If
    
    Dim outputRow As Long
    outputRow = 2
    
    Dim currentSheet As Worksheet
    For Each currentSheet In checkSheets
        Dim lastRow As Long
        lastRow = currentSheet.Cells(Rows.Count, "AB").End(xlUp).Row
    
        Dim i As Long
        For i = 1 To lastRow
            If nameToFind = "" Or currentSheet.Cells(i, "AB").Value = nameToFind Then
                personResponsibleSheet.Cells(outputRow, 2).Value = currentSheet.Cells(i, 1).Value
                personResponsibleSheet.Cells(outputRow, 3).Value = currentSheet.Cells(1, 1).Value
                outputRow = outputRow + 1
            End If
        Next i
    Next currentSheet
    Application.Wait (Now + TimeValue("0:00:01"))
    personResponsibleSheet.Range("B1").Value = personResponsibleSheet.Range("A2").Value
    personResponsibleSheet.Range("C1").Value = personResponsibleSheet.Range("A5").Value
    Application.EnableEvents = True
    Me.Protect Password:="edit"
    

    End Sub

  2. Assistant

    You can modify the search condition to check if any of the words in personResponsibleSheet.Range("A2").Value are found in the sheet range AB by splitting the A2 value into individual words and then checking each word. Here's how you can modify the search condition:

    Sub RunSearch()
    
        Me.Unprotect Password:="edit"
        Application.EnableEvents = False
    
        Dim personResponsibleSheet As Worksheet
        Set personResponsibleSheet = ThisWorkbook.Worksheets("Person Responsible")
    
       ' Clear existing entries in columns B and C, starting from row 2
        personResponsibleSheet.Range("B2:C" & personResponsibleSheet.Cells(Rows.Count, "B").End(xlUp).Row).ClearContents
    
        Dim nameToFind As String
        nameToFind = personResponsibleSheet.Range("A2").Value
    
        Dim sheetToCheck As String
        sheetToCheck = personResponsibleSheet.Range("A5").Value
    
        Dim checkSheets As Collection
        If sheetToCheck = "All Checks" Then
            Set checkSheets = GetCheckSheets ' Search all "Checks" sheets
        ElseIf sheetToCheck <> "" Then
            Set checkSheets = New Collection
            If WorksheetExists(sheetToCheck) Then
                checkSheets.Add ThisWorkbook.Worksheets(sheetToCheck)
            End If
        ElseIf nameToFind <> "" Then
            Set checkSheets = GetCheckSheets ' Search all "Checks" sheets if only name is provided
        Else
            ' No name in A2, no sheet in A5, check all "Checks" sheets
            Set checkSheets = GetCheckSheets
        End If
    
        Dim outputRow As Long
        outputRow = 2
    
        Dim currentSheet As Worksheet
        For Each currentSheet In checkSheets
            Dim lastRow As Long
            lastRow = currentSheet.Cells(Rows.Count, "AB").End(xlUp).Row
    
            Dim i As Long
            For i = 1 To lastRow
                Dim words As Variant
                words = Split(nameToFind, " ")
                Dim word As Variant
                Dim found As Boolean
                For Each word In words
                    If currentSheet.Cells(i, "AB").Value Like "*" & word & "*" Then
                        found = True
                        Exit For
                    End If
                Next word
                
                If nameToFind = "" Or found Then
                    personResponsibleSheet.Cells(outputRow, 2).Value = currentSheet.Cells(i, 1).Value
                    personResponsibleSheet.Cells(outputRow, 3).Value = currentSheet.Cells(1, 1).Value
                    outputRow = outputRow + 1
                End If
            Next i
        Next currentSheet
        Application.Wait (Now + TimeValue("0:00:01"))
        personResponsibleSheet.Range("B1").Value = personResponsibleSheet.Range("A2").Value
        personResponsibleSheet.Range("C1").Value = personResponsibleSheet.Range("A5").Value
        Application.EnableEvents = True
        Me.Protect Password:="edit"
    
    End Sub
    

    This modification will check if any of the words in the nameToFind variable are found in the AB range of each sheet being checked. If any word is found, it will display the data requested in columns B and C of the Person Responsible sheet.