Evaluate string and find word in appropriate worksheet

[JohnnyBravo]

This should be pretty simple for most of you but I can't wrap my head around this. I have a spreadsheet that gets updated frequently. Every day I enter a word in the "Daily Updates" worksheet (column B). It's a continual list of words so the last row today might be 50 but after today's entry, the last row will be 51. So on & so forth. In column C, next to the word, I enter the following formula:

=VLOOKUP($B50, 'Complete Set'!$A$2:$B$2569, 2, FALSE)

The VLOOKUP result will return a string of numbers. Here are a couple of examples:
2,4
1,4

In the first example: 2,4 just 1 number is skipped (the number 3) so then I go the the "1 DV" worksheet....locate the word (in the macro below, the word has been assigned a variabled called "lookupValue"....and then append " X" to the end of the word.

Again, these are just examples. The numbers skipped will always vary. Basically I need to jump to the worksheet named "Y DV" where Y = # of numbers that have been skipped....locate the word... then make the necessary changes on that specific worksheet.

Here are some other examples of the VLOOKUP result:

4,5 - in this case, NO numbers are skipped so then I need to make the necessary changes in the "Consecutive" worksheet.
1,4 - this case, 2 numbers are skipped (numbers 2 & 3) so then I need to make the necessary changes in the "2 DV" worksheet.
1,5 - in this case 3 numbers are skipped (numbers 2 & 3 & 4) so then I need to make the necessary changes in the "3 DV" worksheet.
2,6 - in this case 3 numbers are skipped (numbers 3 & 4 & 5) so then I need to make the necessary changes in the "3 DV" worksheet.


There are 2 exceptions to the above pattern.

If the VLOOKUP result returns 3 sets of numbers (ex: 1,3,5) - then locate the word in the "3 vowels" worksheet and make the necessary changes.
If the VLOOKUP result returns just 1 single digit (ex: 2) - then simply produce a message box "Only 1 digit was returned from the VLOOKUP formula today. vbNewLine & "There were no skips."

I wrote a macro (with the help of chat GPT) that automates the above steps but it is throwing up some unexpected errors. The script works most of the time but occasionally it throws up an error at the Application.Calculate line.

Some other times it works but then it gets up caught up in this section:

        Else
            MsgBox "'" & lookupValue & " was not found in sheet '" & searchSheet & "wksheet!"
        End If

I have no idea why the errors are so inconsistent. Can't figure this out!

Sub ProcessLastEntry_v3()
    Dim wsDUpdates As Worksheet
    Dim wsDWTS As Worksheet
    Dim lastRow As Long
    Dim lookupValue As String
    Dim vlookupResult As Variant
    Dim resultArray() As String
    Dim skipCount As Long
    Dim searchSheet As String
    Dim found As Range
    ' Set the worksheets
    Set wsDUpdates = ThisWorkbook.Sheets("Daily Updates")
    Set wsDWTS = ThisWorkbook.Sheets("Complete Set")
    ' Find the last non-empty row in column B
    lastRow = wsDUpdates.Cells(wsReports.Rows.Count, "B").End(xlUp).Row
    ' Get the last entry from column B
    lookupValue = wsDUpdates.Cells(lastRow, "B").Value
    ' Apply the VLOOKUP formula
    wsDUpdates.Cells(lastRow, "C").Formula = "=VLOOKUP($B" & lastRow & ", 'Complete Set'!$A$2:$B$2569, 2, FALSE)"    
    ' Wait for the formula to calculate
    Application.Calculate
    ' Get the result of the VLOOKUP
    vlookupResult = wsDUpdates.Cells(lastRow, "C").Value
    ' Check if the result is a string and contains a comma
    If IsError(vlookupResult) Then
        MsgBox "VLOOKUP returned an error."
        Exit Sub
    ElseIf InStr(1, vlookupResult, ",") > 0 Then
        resultArray = Split(vlookupResult, ",")        
        ' Count how many numbers are returned
        If UBound(resultArray) = 0 Then
            MsgBox "Only 1 digit was returned from the VLOOKUP formula today. vbNewLine & "There were no skips."
            Exit Sub
        ElseIf UBound(resultArray) = 1 Then
            ' Two numbers
            skipCount = Abs(CInt(resultArray(1)) - CInt(resultArray(0))) - 1            
            If skipCount = 0 Then
                searchSheet = "Consecutive"
            Else
                searchSheet = skipCount & " DV"
            End If
        Else
            ' More than two numbers
            searchSheet = "3 DV"
        End If        
        ' Search for the word in the determined worksheet
        On Error Resume Next ' Enable error handling
        Set found = ThisWorkbook.Sheets(searchSheet).Cells.Find(lookupValue, LookIn:=xlValues, LookAt:=xlPart)
        On Error GoTo 0 ' Disable error handling        
        If Not found Is Nothing Then
            found.Value = found.Value & " X"
        Else
            MsgBox "'" & lookupValue & " was not found in sheet '" & searchSheet & "wksheet!"
        End If
    Else
        ' If the result is a single number
        If IsNumeric(vlookupResult) Then
            Exit Sub ' Do nothing
        Else
            MsgBox "VLOOKUP did not return a valid numeric value."
        End If
    End If
End Sub

[jindon]

First of all, I strongly recommend to force variable declaration by adding Option Explicit on the top.
That will show you the variable(s) that are not declared.

Couple of descrepancy and my guess...
1)

' Find the last non-empty row in column B
' lastRow = wsDUpdates.Cells(wsReports.Rows.Count, "B").End(xlUp).Row
lastRow = wsDUpdates.Cells(wsDUpdates.Rows.Count, "B").End(xlUp).Row

2)

' MsgBox "Only 1 digit was returned from the VLOOKUP formula today. vbNewLine & "There were no skips."
MsgBox "Only 1 digit was returned from the VLOOKUP formula today." & vbNewLine & "There were no skips."

3)

' Set found = ThisWorkbook.Sheets(searchSheet).Cells.Find(lookupValue, LookIn:=xlValues, LookAt:=xlPart)
Set found = ThisWorkbook.Sheets(searchSheet).Cells.Find(vlookupResult, LookIn:=xlValues, LookAt:=xlPart)

[Paul_Hossler]

It's also easier for othesr to take a look if you include an attachment with the troublesome macro and enough data to allow other to test (instructions in my sig)

Also make sure to indicate what you think the expected results should be

[JohnnyBravo]

I didn't mean to ignore you guys. Right after I posted this, my little girl had somewhat of an health situation. She's alright for the time being but it gave us all a scare. I'm gonna put this on the back burner until things calm down. It's been a stressful past couple of days and this Excel project isn't anything really so important.