No need to loop, use COUNTIF or the Find Method;
http://www.ozgrid.com/VBA/find-method.htm


Sub Test()
Dim lFound1 As Long
Dim lFound2 As Long


lFound1 = WorksheetFunction.CountIf(Range("C:C"), "*HA*")
If lFound1 = 0 Then
MsgBox "HA not found - process end"
Exit Sub
End If


lFound2 = WorksheetFunction.CountIf(Range("C:C"), "*HVB*")
If lFound2 = 0 Then
MsgBox "HVB not found - process end"
Exit Sub
End If

End Sub
"Len" wrote in message
...
Hi,

It fails after several attempts, I try to work out VBA codes that help
to find the existence of text string "HA" and "HVB" in C column of
Sheet1and if anyone of text string not found, it will exit

Codes below to find text string " HA " and " HVB" in every row of
column C under sheet1 based on the following conditions : -
1) if "HA" not matched any part of substring in C column, exit sub
with message box " HA not found" or
2) if "HVB" not matched any part of substring in C column, exit sub
with message box " HVB not found"

E.g

Dim WB As Workbook
Dim i As Integer
Set WB = Workbooks("ABC.xls")
For i = 2 To WB.Range("C" & Rows.Count).End(xlUp).Row
If Cells(i, 3).Text Like "*HA*" Then
MsgBox "HA not found - process end"
Exit Sub
Elseif Cells(i, 3).Text Like "*HVB*" Then
MsgBox "HVB not found - process end"
Exit Sub
End If
Next i

Any help will be appreciated and thanks in advance

Regards
Len