Evaluate string and find word in appropriate worksheet
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:
Code:
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!
Code:
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
