Need some help with VBA - evaluating a string of numbers and from VLOOKUP routine.

[Burrgogi]

I have a spreadsheet that I update on a regular basis. The very 1st sheet is called "Daily Tracker Sheet". It's a running list of various words so I designed the macro to look for the very last row and paste the clipboard contents (which is just a single word) into the row just below that. The new entry (from the paste operation) is assigned a variable called vRecent. Then in column C, (next to the word that just got pasted), the macro is designed so that a VLOOKUP formula is entered. The formula will always return a value like so: 2,3 or 1,4. The macro does most of what I want but it's missing 2 key features:

I would like the macro to evaluate the string of numbers & determine how many numbers have been skipped in between the 2 numbers separated by a comma.

Here are some examples:
1) 2,4 (just 1 number is skipped: #3)
2) 2,5 (2 numbers are skipped: #3 & #4)
3) 1,5 (3 numbers are skipped: #1,#2,#3)

The numbers will always be 1 through 9, but never any higher than that. For example, 9,8 or 9,7 are valid, but a value of "10,13" will never exist.

Based on the number of skips, I would like the macro to search for the variable vRecent in the appropriate worksheet.

If just 1 number skipped, find the word in a worksheet called "1 DV" & append (u) to the end of the word.
If there are 2 numbers skipped, find the word in a worksheet called "2 DV" & append (u) to the end of the word.
If there are 3 numbers skipped, find the word in a worksheet called "3 DV" & append (u) to the end of the word.
There will never be more than 3 skips. For example, the VLOOKUP may return something like "1,5" but 1,6 will never be produced.

If on the other hand, there are no numbers skipped such as 3,4, then, find the word in a worksheet called "0 DV" & append (u) to the end of the word.

There are 2 exceptions:
1) If the VLOOKUP value contains 3 series of numbers such as "1,4,5" then no calculation is necessary. Simply go find the vRecent variable in a worksheet called "3 vowels" & append (u) to the end of the word.
2) If the VLOOKUP value contains just 1 single digit with no commas, then ignore it completely.

The 2nd part is I would like the VBA to evaluate the new word that just got pasted (the vRecent) variable. Is the last letter of the word = Y?

If yes, search for the word in a worksheet named "Ends With Y" and append (u) to the end of the word.
If no, then no further action is nessary.

Sub SearchAndAppend_U()
    Dim DTS As Worksheet
    Dim YON As Worksheet
    Dim PARS As Worksheet
    Dim lastRow1 As Long
    Dim lastRow2 As Range
    Dim c As Range
    Dim c2 As Range
    Dim vRecent As String
    Dim tbl As ListObject
 
    Set Tbl1 = ThisWorkbook.Worksheets("Used Words").ListObjects("Strictly_Used_List")
    Set DTS = ThisWorkbook.Sheets("Daily Tracker Sheet")
    Set YON = ThisWorkbook.Sheets("Yes or No")
    Set PARS = ThisWorkbook.Sheets("Parse Sheet")

With DTS
    lastRow1 = DTS.Range("B" & Rows.Count).End(xlUp).Row
    DTS.Range("B" & lastRow1).Offset(1, 0).Select
    DTS.Paste

vRecent = ActiveCell.Value
ActiveCell.Offset(0, 1).Formula = "=VLOOKUP(RC2,'Comprehensive Listing'!R2C1:R2592C3,2,FALSE)"
ActiveCell.Offset(0, 2).Select
End With

With Sheets("Used Words")
    If Tbl1.ShowAutoFilter = True Then  ' Make sure there are no active filters.
        Tbl1.AutoFilter.ShowAllData
    Set lastRow2 = Tbl1.ListColumns(1).Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1) ' find & set the last row variable
    lastRow2.Value = vRecent
    lastRow2.Offset(0, 1).Formula = "=VLOOKUP(RC1,'Comprehensive Listing'!R2C1:R2592C3,3,FALSE)"
    End If
End With

With YON
lastRow3 = YON.Range("A" & Rows.Count).End(xlUp).Row
    Set c = YON.Range("A2:A" & lastRow3).Find(vRecent, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If c Is Nothing Then
    MsgBox "No match found for word: " & targetWord, vbExclamation
    Else
        c.Value = c.Value & " (u)"
    End If
End With

With PARS
Set c2 = PARS.Range("A2:A" & lastRow3).Find(vRecent, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If c2 Is Nothing Then
    MsgBox "No match found for word: " & targetWord, vbExclamation
    Else
        c2.Value = c2.Value & " (u)"
    End If
End With

End Sub

 

[Burrgogi]

No one has an idea on this?

 

[StephenCrump]

No one has an idea on this?

The lack of answers probably reflects more on the question ....

You have two VLOOKUP's, and it's not clear which one is returning the string like "2,4".

Perhaps you could structure your code along these lines, once you have the string value, which I have called s.

s = "6,9"  'or single value "5", or triple value "1,4,5", say

t = Split(s, ",")
r = UBound(t) + 1

If r = 2 Then
    N = Application.Max(0, Abs(t(1) - t(0)) - 1)
    Set ws = Worksheets(N & " DV")
    'do your finding in ws
ElseIf r = 3 Then
    'Find vRecent in Worksheet("3 vowels")
Else
    '??
End If

I would like the macro to search for the variable vRecent in the appropriate worksheet.

If just 1 number skipped, find the word in a worksheet called "1 DV" & append (u) to the end of the word.

This is a bit vague. Where exactly do you "find the word". And presumably you're looking for a second word that lines up with vRecent, otherwise you could simply append "u" to the value of vRecent?