VBA to evaluate if a certain letter exists in string

Burrgogi

Well-known Member
Joined
Nov 3, 2005
Messages
502
Office Version
  1. 2010
Platform
  1. Windows
I have a very simple but very repetitive task that I would like some help on.
I have a word that gets entered into sheet1, column A. It's a rolling list of words so today the last row is 50 but tomorrow the last row will be 51... the day after that will be row 52, etc,.
Whatever is entered there, I would like the macro to store that as a string variable and do the following:

1) Does the word end in 'Y?
If yes, then search for the word in ThisWorkbook.Sheets("Ends with Y") and mark that cell in bold font.
Otherwise, produce a message box indicating that the word was not found and exit sub

2) Does the word contain 'Y' anywhere in the string? EXCEPT for the last letter
If yes, then search for the word in ThisWorkbook.Sheets("Contains Y") and mark that cell in bold font.
Otherwise, produce a message box indicating that the word was not found and exit sub

There will be some cases where both conditions are met such as YUMMY in which case the search should be done in ThisWorkbook.Sheets("Ends with Y").

It's very simple to have the VBA macro to grab the last letter, using something like this: MyKeyWord_lastLetter = Right(c2, 1)

The problem is that the length of the word will always vary and that particular solution does not address 'Y' occurring in other parts of the string (beginning, middle, etc).
 
Is it something like this?

This goes in the sheet code for the sheet you enter then values into, adjust the names etc to suit.

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tWord As String
Dim Found As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    tWord = UCase(Target.Value)
      
    If Right(tWord, 1) = "Y" Then
        ' Ends Y
        Set Found = Sheets("End Y").Range("A:A").Find(Target.Value, Range("A1"))
        If Not Found Is Nothing Then Found.Font.Bold = True
    ElseIf InStrRev(Left(tWord, Len(tWord) - 1), "Y") Then
        ' Contains Y
        Set Found = Sheets("Contains Y").Range("A:A").Find(Target.Value, Range("A1"))
        If Not Found Is Nothing Then Found.Font.Bold = True
    Else
        MsgBox "No Y"
    End If
    
End If

End Sub
 
Upvote 0
I customized a message box to read "Today's key word does not contain any Y"

It's not working at all. I tested by using a word like Happy and it's not detecting the Y at the end.
 
Upvote 0
Not clear about case sensitivity...

Case sensitive and find Exact match.
So only find capital "Y", not "y" in the string.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s, wsName$, r As Range
    If Intersect(Target, Columns("a")) Is Nothing Then Exit Sub
    s = Target.Value
    If Not s Like "*Y*" Then MsgBox "Today's key word does not contain any Y": Exit Sub
    wsName = IIf(s Like "*Y", "Ends with ", "Contains ") & "Y"
    Set r = Sheets(wsName).Cells.Find(s, , -4163, 1, , , True, False, False)
    If Not r Is Nothing Then
        r.Font.Bold = True
        Target.Font.Bold = True
    Else
        MsgBox """" & s & """ Is Not found in """ & wsName & """"
    End If
End Sub
If case insensitive
1) change 2 Y in bold to [Yy]
2) change True to False
 
Upvote 0
I customized a message box to read "Today's key word does not contain any Y"

It's not working at all. I tested by using a word like Happy and it's not detecting the Y at the end.

How did you implement it? In what module did you place the code?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top