Find Text String

[Len]

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

 

[JLGWhiz]

Here is how I interpreted your post. If a cell in column C does not contain
characters "HA" nor "HVB" then exit the macro, otherwise check the next
cell. Repeat the process untill all cells in column C are checked.
If that is correct then try the code below.


Dim WB As Workbook, sh As Worksheet
Dim i As Integer
Set WB = Workbooks("ABC.xls")
Set sh = ActiveSheet
With WB
For i = 2 To sh.Range("C" & Rows.Count).End(xlUp).Row
If InStr(sh.Cells(i, 3), "HA") = 0 Then
x = x + 1
End If
If InStr(sh.Cells(i, 3), "HVB") = 0 Then
y = y + 1
End If
If x + y = 0 Then
MsgBox "Not Found"
Exit Sub
Else
x = 0
y = 0
End If

Next i
End With

 

[ozgrid.com]

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]

Hi All,

Thanks for your quick reply and your codes

Ozgrid, thanks for your reference of website

JL, yes! you are right and this is the codes that I'm looking for
However, after I work around based on the suggested codes provided by
JL, there is no response
Then I try to modify the codes as follows and it is not completely
worked, please help

Dim WB As Workbook, sh As Worksheet
Dim i As Integer
Set WB = Workbooks("ABC.xls")
Set sh = ActiveSheet
With WB
For i = 2 To sh.Range("C" & Rows.Count).End(xlUp).Row
If InStr(sh.Cells(i, 3), "HA") = 0 Then
x = x + 1
End If
If InStr(sh.Cells(i, 3), "HVB") = 0 Then
y = y + 1
End If
If x = 0 Then
MsgBox "HA Not Found"
Exit Sub
Else
x = 0
End If
If y = 0 Then
MsgBox "HVB Not Found"
Exit Sub
Else
y = 0
End If

Next i
End With

Thanks & Regards
Len

 

[JLGWhiz]

Hi Len, Sorry I didn't get back quicker. Try the code below. I think it
will do the trick.

Dim WB As Workbook, sh As Worksheet
Dim i As Integer
Set WB = ActiveWorkbook
Set sh = ActiveSheet
With WB
For i = 2 To sh.Range("C" & Rows.Count).End(xlUp).Row
If InStr(sh.Cells(i, 3), "HA") = 0 And _
InStr(sh.Cells(i, 3), "HVB") = 0 Then
MsgBox "Not Found"
Exit Sub
End If

Next i
End With

 

[Len]

Hi JL,

Thanks for your codes and it works great !


Cheers
Len