Problem with basic looping and criteria

[Guest]

I am trying to loop through a range and count the number of time a cell
contains the letters"CPCT". I am using the right function since they always
occur at the end of the string. My cod keeps getting snagged here: If
xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then

Any Ideas? My full code is below
Dim r1 As Range
Dim Count As Integer
Count = 0
For Each r1 In xlApp.Sheets("Resource Info").Range("A4:A500")
If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then
Count = Count + 1
End If
Next

 

[Guest]

Sub What()
Count = 0
Range("A4").Select
Do Until ActiveCell.Address = "$A$500"
If Right(ActiveCell.Value, 4) = "CPCT" Then
Count = Count + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Guest]

Try this...

Dim r1 As Range
Dim Count As Integer
Count = 0
For Each r1 In Range("A4:A500")
If Right(r1.Value, 4) = "CPCT" Then
Count = Count + 1
End If
Next
MsgBox "total = " & Count