Searching for specific words in the string data with VBA

[Thudika]

Hi, I wrote the following code to loop through data column for keywords such as "personal" or "fraud" and copy rows with these keywords to a separate tab. The problem is that my code does not work when the keyword is within the phrase (i.e. "personal expenses"). Is there quick fix for that? Thanks!

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub pooling()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sheet1").Cells(i, 10).Text = "Personal" Or _
Worksheets("Sheet1").Cells(i, 10).Text = "Govt" Then
Worksheets("Sheet1").Rows(i).Copy
Worksheets("Sheet2").Activate
b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("sheet1").Activate
End If
Next
End Sub</code>

 

[shg]

Welcome to the board.

Try this:

Option Explicit

Sub pooling()
  Dim iRow          As Long
  Dim wks           As Worksheet

  Set wks = Worksheets("Sheet1")

  With wks
    For iRow = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
      If InStr(.Cells(iRow, "J").Text, "Personal") Or _
         InStr(.Cells(iRow, "J").Text, "Govt") Then
        .Rows(iRow).Copy Worksheets("Sheet2").Cells(.Rows.Count, 1).End(xlUp)(2)
      End If
    Next iRow
  End With
End Sub

 

[Thudika]

Thanks for your help! That works.

 

[shg]

You're welcome.