Burrgogi
Active Member
- Joined
- Nov 3, 2005
- Messages
- 496
- Office Version
- 2010
- Platform
- Windows
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.
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.
VBA Code:
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
Last edited: