Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text String
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text String
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 "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text String
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text String
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text String
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Text String
Hi JL,
Thanks for your codes and it works great ! Cheers Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Find text within a string | Excel Programming | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
backwards find function to find character in a string of text | Excel Programming | |||
How to Find text in String? | Excel Programming |