If Cell Begins with Text, Msg box

[jcipriano13]

hi, I'm trying to create a line in my macro to show a msg box if cells in a give range begin with a certain text. For example, if any cell in column O begins with "ES", msg box "Spain Trades"(no action if not). Thanks!

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 4/24/2018 12:10 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "O").End(xlUp).Row
    For i = 1 To Lastrow
        If Left(Cells(i, "O"), 2) = "ES" Then MsgBox "Spain Trades"
    Next
Application.ScreenUpdating = True
End Sub

 

[BenGee]

This worked for me;

    For Each c In Range("O:O")
        If Left(c.Value, 2) = "ES" Then
            MsgBox ("Spain Trades")
        End If
    Next

 

[Rick Rothstein]

hi, I'm trying to create a line in my macro to show a msg box if cells in a give range begin with a certain text. For example, if any cell in column O begins with "ES", msg box "Spain Trades"(no action if not). Thanks!

While it will not display a MessageBox, you might want to consider using Conditional Formatting (maybe color cells starting with "ES" red for example) instead of a macro.