Having trouble with error handling

[Burrgogi]

The following is a straight forward macro that I wrote. It has a string variable named "Key_Word" and the macro is designed to search for that key_word in 3 different worksheets. The final step is to check if the macro contains the letter Y. If it does not, then I want to display a message box "Today's answer does contain any Y", and then quit. You can jump down to the end where I've got the following comment line:

    ' Condition 3: If the word does NOT contain "Y" anywhere

It mostly works but it keeps tripping up on the very last section of the macro. It does display the message box when the word doesn't contain any Y, but it errors after that and doesn't know what to do. I then inserted a new line: "On Error GoTo 0" to take care of the VBE error, but for some reason I can't figure out, it is still tripping up after the message box.

Just learning some VBA and so my error trapping skills are still newbish. Some help would be appreciated.

Sub Find_Key_Word ()

With UONS
    On Error Resume Next
    lastRow3 = UONS.Range("A" & Rows.Count).End(xlUp).Row
    Set c = UONS.Range("A2:A" & lastRow3).Find(Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not c Is Nothing Then
        c.Value = c.Value & MarkIt
    Else
        MsgBox "Could not find: " & Key_Word & " in the" & UONS & "worksheet!"
    End If
End With

With PARS
    On Error Resume Next
    Set c2 = PARS.Range("A2:A" & lastRow3).Find(Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not c2 Is Nothing Then
        c2.Value = c2.Value & MarkIt
        c2.Offset(, 1).Value = 1
    Else
        MsgBox "Could not find: " & Key_Word & " in the" & PARS & "worksheet!"
    End If
End With

With CSET
    On Error Resume Next
    Set c3 = CSET.Range("A2:A" & lastRow3).Find(Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not c3 Is Nothing Then
        c3.Offset(, 1).Value = 1
    Else
        MsgBox "Could not find: " & Key_Word & " in the" & CSET & "worksheet!"
    End If
End With


' this section processes words that end in Y or contain Y
    ' Condition 1: Check if the word ends with "y"
 
    If Key_Word Like "*y" Then
        ' Search for the word in "Ends with Y" sheet
        Set foundCell = wsEndsWithY.Cells.Find(What:=Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        Set foundCell2 = wsContainsY.Cells.Find(What:=Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not foundCell Is Nothing Then
            'foundCell.Font.Bold = True
            foundCell.Value = foundCell.Value & MarkIt
        End If
        If Not foundCell2 Is Nothing Then
            'foundCell2.Font.Bold = True
            foundCell2.Value = foundCell2.Value & MarkIt
            foundCell2.Offset(, 1).Value = 1
        End If
    End If
 
    ' Condition 2: Check if the word contains "Y" anywhere else
    If InStr(1, Key_Word, "Y", vbTextCompare) > 0 And Not (keyword Like "*y") Then
        ' Search for the word in "Contains letter Y" sheet
        Set foundCell = wsContainsY.Cells.Find(What:=Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        Set foundCell2 = wsContainsY2.Cells.Find(What:=Key_Word, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not foundCell Is Nothing Then
            'foundCell.Font.Bold = True
            foundCell.Value = foundCell.Value & MarkIt
            foundCell.Offset(, 1).Value = 1
        End If
        If Not foundCell2 Is Nothing Then
            'foundCell2.Font.Bold = True
            foundCell2.Value = foundCell2.Value & MarkIt
            foundCell2.Offset(, 1).Value = 1
        End If
    End If
 
    ' Condition 3: If the word does NOT contain "Y" anywhere
    On Error GoTo 0
    If InStr(1, Key_Word, "Y", vbTextCompare) = 0 Then
        MsgBox "Today's answer does not contain any Y!"
        Exit Sub
    End If

End Sub

To be clear, I am getting a "Code Execution has been interrupted". When I click on 'Debug', the End If line is highlighted. I've tried swapping the order of the "Exit Sub" line and the "End If" line around but it doesn't seem to make any difference.

MsgBox "Today's answer does not contain any Y!"
End If
Exit Sub

 

[Burrgogi]

Since your original DTS code was working replace my DTS section with yours and rerun it.

I did that and I'm right back to square 1 again. I get the same exact error message as before. "Code Execution has been interrupted."

Debug highlights the End If line that appears at the end:

    ' Condition 3: If the word does NOT contain "Y" anywhere
    On Error GoTo 0
    If InStr(1, DailyWordle, "Y", vbTextCompare) = 0 Then
        MsgBox "Today's answer does not contain any Y!"
    End If
    'Exit Sub

End Sub

One more thing Alex, I should add that when the key_word happens to be something like "fully" (which ends with the letter 'Y') everything works perfectly without any errors. The error msg pops up only when there is no Y.

 

[Burrgogi]

There is definitely something very weird going on with the macro. I've done some additional testing with the modified code you gave me using a variety of Key_Words that involve letter Y and some that don't. I'm getting errors at random points. One time it errored out on the End With in the section noted below. Other times it errors out on the End If line like I mentioned in my previous posting.

    With ThisWorkbook.Sheets("Used Words Sheet").ListObjects("Used_Words_List")
        'shift from an extra row if list has header
        Dim iHeader As Long
        If .ShowHeaders Then
            iHeader = 1
        Else
            iHeader = 0
        End If
       'find the last row of the list
        tLastRow = .ListRows.Count + 1
       .Range(tLastRow + iHeader, "A").Value = Key_Word
    End With

OK, disregard my comment above. I realized I made an error when testing.

Where things stand now is that I'm right back to square one when I originally posted my question. If the Key_Word does not contain the letter Y, it errors out.

 

[Alex Blakenburg]

I can't replicate the issue you are having.
Is there any way that you can share your workbook via Google drive, drop box, onedrive etc ?
You would need to give public access to anyone with the link.

I also need an example scenario to test that is giving you the issue.
PS: What is the value in B last row and C last row when it fails ?

 

[Burrgogi]

I would have to think about that. It's not like the workbook contains some world class secrets but I was hoping to keep some of the worksheets private to myself. I'll post back here if I decide to proceed further. The code runs 99% perfectly. The error message only happens when the the key_word value doesn't contain any 'Y' so.... it's not exactly the end of the world. Just annoying & perplexing is all.

 

[Alex Blakenburg]

If you can strip the data from the workbook and can still reproduce the error then we don't need to see much actual information.
I have tried with and without a Y in Key_Word and can't reproduce it.
You could try using F8 and see if the error shows up on a more specific line. End With / End If seem strange places for it to error out on in an inconsistent manner.