software

I need to act upon the test quot;USAquot; 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 copare against specifc characters?
Even better, can I use regular expressions?

str = ActiveSheet.Cells(r, c)
check = substr(str, 1, 3)
If (check = quot;USAquot;) Then
' do something
End IfHi Samuel,

Try:

Dim sStr As String

sStr = ActiveSheet.Cells(r, c).Value

If Left(sStr, 3) = quot;USAquot; Then
'Do something
End If---
Regards,
Norman
quot;Samuelquot; gt; wrote in message oups.com...
gt;I need to act upon the test quot;USAquot; found in the first three chars of a
gt; cell.
gt; This is what I did, but I get a compile error..
gt; So I did something wrong. How do I copare against specifc characters?
gt; Even better, can I use regular expressions?
gt;
gt; str = ActiveSheet.Cells(r, c)
gt; check = substr(str, 1, 3)
gt; If (check = quot;USAquot;) Then
gt; ' do something
gt; End If
gt;
The compile error may be because of the name of the variable quot;strquot;, 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 quot;Microsoft
VBScript Regular Expresions 5.5quot;

Hope this helps,
Miguel.

quot;Samuelquot; wrote:

gt; I need to act upon the test quot;USAquot; found in the first three chars of a
gt; cell.
gt; This is what I did, but I get a compile error..
gt; So I did something wrong. How do I copare against specifc characters?
gt; Even better, can I use regular expressions?
gt;
gt; str = ActiveSheet.Cells(r, c)
gt; check = substr(str, 1, 3)
gt; If (check = quot;USAquot;) Then
gt; ' do something
gt; End If
gt;
gt;

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 quot;next' added within the new USA compare...How do I break
out?
For i = 1 To lastrow
str1 = ActiveSheet.Cells(r, c1)
If Left(str1, 3) = quot;USAquot; Then
...do something
Next
End If
...do something
NextYou can break the loop with an EXIT FOR statement inside the IF construction.

Miguel.

quot;Samuelquot; wrote:

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

I'm sorry, Miguel, but I misphrased it. I want to continue with the
next iteration. Not break out complelty.
If I change the 'next' to a 'next for' i get the same error.I am not sure if you can jump to the next iteration on VBA, maybe you can ask
the question on the excel.programming newsgroup.

Sorry for not being able to help more,
Miguel.

quot;Samuelquot; wrote:

gt; I'm sorry, Miguel, but I misphrased it. I want to continue with the
gt; next iteration. Not break out complelty.
gt; If I change the 'next' to a 'next for' i get the same error.
gt;
gt;

you were great! thank you!Hi Samuel,

Try something like:

'=============gt;gt;
Public Sub Tester()
Dim sStr As String
Dim i As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row

For i = 1 To LastRow
sStr = ActiveSheet.Cells(i, quot;Aquot;).Value
If Left(sStr, 3) = quot;USAquot; Then
'Do something, e.g.:
Cells(i, quot;Aquot;).Interior.ColorIndex = 6
Else
'Do something else
End If
Next i

End Sub
'lt;lt;=============---
Regards,
Norman
quot;Samuelquot; gt; wrote in message oups.com...
gt; I'm sorry, Miguel, but I misphrased it. I want to continue with the
gt; next iteration. Not break out complelty.
gt; If I change the 'next' to a 'next for' i get the same error.
gt;