0

Encountering an error on my code when trying to move the rows to another sheet.

I have a list of keywords that I would like to find in a list of data in a sheet called "Today". If it's found in the list, then it will move to another sheet called "Exception". I already have a working code, however, I am encountering an error "Code execution has been interrupted".

List of data that I have which I need to look for a certain keyword: 

| Assignee | Due on/At  | Attachment    | Subject Description |
|----------|------------|---------------|---------------------|
| Carl     | 16.11.2016 | No Attachment | Re: Information 1   |
| Clark    | 16.11.2016 | No Attachment | Test 4              |
| Kent     | 16.11.2016 | No Attachment | Test 6              |
| Japhet   | 16.11.2016 | No Attachment | Test 6              |
| Ryza     | 16.11.2016 | No Attachment | Re: Information 2   |
| Shane    | 16.11.2016 | No Attachment | FWD Subject 1       |
| Kent     | 16.11.2016 | No Attachment | Test 6              |
| Japhet   | 16.11.2016 | No Attachment | Test 6              |
| Ryza     | 16.11.2016 | No Attachment | FWD Subject 2       |
| Shane    | 16.11.2016 | No Attachment | Test 8              |
| Shane    | 16.11.2016 | No Attachment | Test 92             |
| Japhet   | 16.11.2016 | No Attachment | R:                  |
| Japhet   | 16.11.2016 | No Attachment | Test 92             |

List of keywords I set: 

| //// Exception Keywords |
|-------------------------|
| Re:                     |
| R:                      |
| FWD                     |
| Test                    |
| FW                      |

The expectation for this, it will move all the rows in another sheet that contains a specific keyword which I have listed. In this case, it will be the rows of:

  1. Re: Information 1
  2. Re: Information 2
  3. FWD Subject 1
  4. FWD Subject 2
  5. R:

By the way, the list of keywords can grow.

Here is my code:

Sub SeparateExceptionList()

Dim MainSheet as Worksheet
Dim TodaySheet as Worksheet
Dim excLastRow As Long
Dim tLastRow as Long
Dim i as long
Dim j as long

Set MainSheet = Sheets("Main")
Set TodaySheet = Sheets("Today")


tLastRow = TodaySheet.Cells(Rows.Count, 4).End(xlUp).Row
excLastRow = MainSheet.Cells(Rows.Count, 7).End(xlUp).Row

For j = 10 To excLastRow

exceptionKeyword = MainSheet.Cells(j, 7).Value

    For i = tLastRow To 2 Step -1

    If UCase(TodaySheet.Cells(i, 4)) Like "*" & UCase(exceptionKeyword) & "*" Then

        TodaySheet.Range("a" & i & ":D" & i).Copy Sheets("Exception").Range("ExceptionTable").ListObject.ListRows.Add.Range
        TodaySheet.Cells(i, 4).EntireRow.Delete '//This is where the code is being interrupted

        Else:

    End If

    Next i

Next j

End Sub
Sevpoint
  • 213
  • 1
  • 8
  • 26
  • without an example of your data (text, not a screen shot), it's hard to say what's going wrong. But it might be simpler and faster to just use the **Advanced Filter**. – Ron Rosenfeld Jan 13 '19 at 12:22
  • Hi I've edited and put some screenshot and data. – Sevpoint Jan 13 '19 at 13:45
  • I'd still suggest a filter. BTW, your screenshot of data is virtually useless for doing proper troubleshooting. It cannot be copy/pasted into a worksheet. One can try an OCR program, or manually enter it. To do either of these is discouraging to those who might assist you. To make the data useful edit your question to post it as text, perhaps using this [Markdown Tables Generator](http://www.tablesgenerator.com/markdown_tables), or possibly upload a workbook that demonstrates the problem (with sensitive information removed) to some public website and post a link in your original question – Ron Rosenfeld Jan 13 '19 at 13:58
  • And you can certainly implement the filter in code, if needed. – Ron Rosenfeld Jan 13 '19 at 13:59
  • Thanks for the suggestion, I have edited the the sample data into text. How would I do it with the Advance filter? – Sevpoint Jan 13 '19 at 14:29
  • 1
    I found the solution in [Excel VBA App stops spontaneously with message “Code execution has been halted”](https://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted) and realized that the code is working as I expected. – Sevpoint Jan 13 '19 at 14:45
  • Glad you've got it working. – Ron Rosenfeld Jan 13 '19 at 15:19

0 Answers0