In the following VBA, I need to change the search condition of 'nameToFind =…
- 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
- 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 SubThis modification will check if any of the words in the
nameToFindvariable are found in theABrange of each sheet being checked. If any word is found, it will display the data requested in columns B and C of thePerson Responsiblesheet.