Using Regular Expression than Substring in Microsoft Excel

[Jigisha]

I need to act upon the test "USA" found in the first three chars of a cell. This is what I did, but I get a compile error. So I did something wrong. How do I compare against specifc characters. Even better, can I use regular expressions.

str = ActiveSheet.Cells(r, c)
check = substr(str, 1, 3)
If (check = "USA") Then
' do something
End If

[BillieJ]

Try this out.

Dim sStr As String
sStr = ActiveSheet.Cells(r, c).Value
If Left(sStr, 3) = "USA" Then
'Do something
End If

[JustineBB]

The compile error may be because of the name of the variable "str", try with str1, for example. About functions to play with text, try LEFT, MID or RIGHT. In this case, LEFT can be the best one. You can use Regular Expresions in VBA, just add the reference for "Microsoft VBScript Regular Expresions 5.5"

[Jigisha]

Thank you, that gets rid of that problem, but brings up a second one - How do I break out of a loop. If I have the following, I now get a compile error (next without for) on the new "next' added within the new USA compare...How do I break out. For i = 1 To lastrow , tr1 = ActiveSheet.Cells(r, c1). If Left(str1, 3) = "USA" Then do something Next End If do something.

[-Deven-]

You can break the loop with an EXIT FOR statement inside the IF construction.

[JyotiT]

Try something like this :

Public Sub Tester()
Dim sStr As String
Dim i As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
sStr = ActiveSheet.Cells(i, "A").Value
If Left(sStr, 3) = "USA" Then
'Do something, e.g.:
Cells(i, "A").Interior.ColorIndex = 6
Else
'Do something else
End If
Next i
End Sub