VeryForgetful
Board Regular
- Joined
- Mar 1, 2015
- Messages
- 242
Hi All,
I am using the code below to search for a keyword in a string against a list of keywords located in another tab, if the word exists then the row is copied over to another sheet.
The code works, however it has to be run twice to complete successfully. I am using a nested For Each Loop, the outer one is to loop through each keyword in the list and the inner loop for each cell where the string needs to be checked. I have attached a copy of the workbook if anyone can assist please?
https://1drv.ms/x/s!AilrZgAqVaaqmTEk0Ku7G8iY4uSv
I am using the code below to search for a keyword in a string against a list of keywords located in another tab, if the word exists then the row is copied over to another sheet.
The code works, however it has to be run twice to complete successfully. I am using a nested For Each Loop, the outer one is to loop through each keyword in the list and the inner loop for each cell where the string needs to be checked. I have attached a copy of the workbook if anyone can assist please?
https://1drv.ms/x/s!AilrZgAqVaaqmTEk0Ku7G8iY4uSv
Code:
Sub CopyRows()
Dim SrcSheet As Worksheet
Dim DstSheet As Worksheet
Dim KeywordSheet As Worksheet
Dim LrSrc As Long
Dim c As Range
Dim x As Range
Dim LookupRange As Range
Dim LrLookup As Long
Set SrcSheet = Sheets("Raw Data")
Set DstSheet = Sheets("Filtered Results")
Set KeywordSheet = Sheets("Keyword Search")
SrcSheet.Columns.AutoFit
LrSrc = SrcSheet.Range("A" & Rows.Count).End(xlUp).Row
LrLookup = KeywordSheet.Range("A" & Rows.Count).End(xlUp).Row
Set LookupRange = KeywordSheet.Range("A2:A" & LrLookup)
For Each x In LookupRange
For Each c In SrcSheet.Range("A2:A" & LrSrc)
Debug.Print "Checking for keyword: " & x.Value; " in string " & c.Value
Application.StatusBar = "Checking for keyword: " & x.Value
If InStr(UCase(c.Value), UCase(x.Value)) > 0 Then 'convert both values to uppercase to compare
c.EntireRow.Copy DstSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
c.EntireRow.Delete Shift:=xlUp
End If
Next c
Next x
DstSheet.Columns.AutoFit
Application.StatusBar = False
End Sub
Last edited: