Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
Hi All,
I am new in Excel programming. I need to check if a cell contains a substring, something like "*excel*". First, I tried Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Only it looks like .Find cannot be used more than once. I have a nested loop and used .Find in a wrapper loop. I need some alternative to .Find. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
Check out this link for a formula:
http://www.exceltip.com/st/Finding_a...tring/874.html If you want to do this from VBA using a formula, see VBHelp on "Evaluate" Mike F "Vik" wrote in message ... Hi All, I am new in Excel programming. I need to check if a cell contains a substring, something like "*excel*". First, I tried Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Only it looks like .Find cannot be used more than once. I have a nested loop and used .Find in a wrapper loop. I need some alternative to .Find. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
The following code found all instances of the substring "excel" within the
range A1:C10. The Selection and MsgBox code is for demo purposes only. I think what you are looking for is the FindNext method. Note that, here, "d" serves as the "After" argument to FindNext (i.e. FindNext(d)). In other words, find the next cell containing the substring, skipping the first cell. The code then sets d to this new cell and so forth. Also note that the FindNext method will form a nonterminating loop, finding the first cell again once it comes to the end of the range and repeating from there. That's why we record the address of the first instance (adr) and stop it at this point. Sub FindSubString() Dim d As Range Dim rng As Range Dim cdnName As String Dim adr As String cdnName = "excel" Set rng = Range("A1:C10") Set d = rng.Find(cdnName) If Not d Is Nothing Then d.Select MsgBox d.address adr = d.Address ' record address of first instance Do Set d = rng.FindNext(d) d.Select MsgBox d.Address Loop Until d.Address = adr ' stop when loops around to first instance End If End Sub "Vik" wrote: Hi All, I am new in Excel programming. I need to check if a cell contains a substring, something like "*excel*". First, I tried Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Only it looks like .Find cannot be used more than once. I have a nested loop and used .Find in a wrapper loop. I need some alternative to .Find. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
Greg,
Let me be more specific. Let's say I have a datasheet as follows. ____A______B__ 1 aaaa 2 aaaa exc1 3 aaaa 3exc 4 bbbb exc2 5 bbbb 6 bbbb Below is my macro - Sub test() Sheets("TEST").Activate Dim rng1 As Range Dim rng2 As Range Dim c As Range Dim d As Range Set rng1 = ActiveSheet.Range("A1:A6") Set c = rng1.Find("aaaa", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then firstAddress = c.Address cntAaaE = 0 cntAaaNoE = 0 Do myRow = c.Row Set rng2 = ActiveSheet.Cells(myRow, 2) Set d = rng2.Find("exc", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) If Not d Is Nothing Then cntAaaE = cntAaaE + 1 Else cntAaaNoE = cntAaaNoE + 1 End If Set c = rng1.FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If MsgBox "cntAaaE=" & cntAaaE & ", cntAaaNoE=" & cntAaaNoE End Sub I expected it to print "cntAaaE=2, cntAaaNoE=1". I need to count lines that match "aaaa" in a 1st column AND having "exc" as a part of a value in a 2nd column. But unfortunately, rng1.FindNext(c) does not do what I want. Appreciate your help. "Greg Wilson" wrote in message ... The following code found all instances of the substring "excel" within the range A1:C10. The Selection and MsgBox code is for demo purposes only. I think what you are looking for is the FindNext method. Note that, here, "d" serves as the "After" argument to FindNext (i.e. FindNext(d)). In other words, find the next cell containing the substring, skipping the first cell. The code then sets d to this new cell and so forth. Also note that the FindNext method will form a nonterminating loop, finding the first cell again once it comes to the end of the range and repeating from there. That's why we record the address of the first instance (adr) and stop it at this point. Sub FindSubString() Dim d As Range Dim rng As Range Dim cdnName As String Dim adr As String cdnName = "excel" Set rng = Range("A1:C10") Set d = rng.Find(cdnName) If Not d Is Nothing Then d.Select MsgBox d.address adr = d.Address ' record address of first instance Do Set d = rng.FindNext(d) d.Select MsgBox d.Address Loop Until d.Address = adr ' stop when loops around to first instance End If End Sub "Vik" wrote: Hi All, I am new in Excel programming. I need to check if a cell contains a substring, something like "*excel*". First, I tried Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Only it looks like .Find cannot be used more than once. I have a nested loop and used .Find in a wrapper loop. I need some alternative to .Find. Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if a cell contains a substring
Mike,
On the web I have found 2 solutions - Solution 1: Using the COUNTIF function in the following IF statement: =IF(COUNTIF(A2,""*""&B2&""*"")0,""Found"",""Not Found"") Solution 2: Using the ISNUMBER and FIND functions, as follows: =IF(ISNUMBER(FIND(B2,A2)),""Found"",""Not Found"") I tried to follow, but I cannot figure out the syntax - tmp = Evaluate(CountIf(A2, "*exc*") 0, "Found", "Not Found") Should I put the formula into some cell and evaluate it? Thanks "Mike Fogleman" wrote in message ... Check out this link for a formula: http://www.exceltip.com/st/Finding_a...tring/874.html If you want to do this from VBA using a formula, see VBHelp on "Evaluate" Mike F "Vik" wrote in message ... Hi All, I am new in Excel programming. I need to check if a cell contains a substring, something like "*excel*". First, I tried Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Only it looks like .Find cannot be used more than once. I have a nested loop and used .Find in a wrapper loop. I need some alternative to .Find. Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
First, be advised that my comments on the Find method are based only on a
modest amout of experience. My understanding is that the FindNext method continues the search started by the previous use of the Find method. However, your code reuses the Find method and resets the What argument (string to be looked for) to "exe" from "aaaa". It then goes on to use FindNext(c) with the incorrect assumption that it will continue looking for "aaaa". I rewrote your code as shown below with some adaption to personal style. I instead resorted to the Like operator thereby eliminating the need to reuse Find. Note that I also establish the identity of the neighboring cell using Set cc = c(1, 2). This is the same as using Set cc = c.Offset(0, 1), just a little more concise. This is more efficient than getting the row number and then using the Cells method: "Cells(myRow, 2)". Also note that I refrain from activating sheet "TEST" which is unnecessary. Sub test() Dim rng As Range Dim c As Range, cc As Range Dim adr As String Dim x As Long, xx As Long Dim ws As Worksheet Set ws = Sheets("TEST") x = 0: xx = 0 Set rng = ws.Range("A1:A12") Set c = rng.Find("aaaa", LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then adr = c.Address Do Set cc = c(1, 2) 'or c.Offset(0, 1) If cc.Value Like "*ece*" Then x = x + 1 Else xx = xx + 1 End If Set c = rng.FindNext(c) Loop While c.Address < adr End If MsgBox "x=" & x & ", xx=" & xx End Sub "Vik" wrote: Greg, Let me be more specific. Let's say I have a datasheet as follows. ____A______B__ 1 aaaa 2 aaaa exc1 3 aaaa 3exc 4 bbbb exc2 5 bbbb 6 bbbb Below is my macro - Sub test() Sheets("TEST").Activate Dim rng1 As Range Dim rng2 As Range Dim c As Range Dim d As Range Set rng1 = ActiveSheet.Range("A1:A6") Set c = rng1.Find("aaaa", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then firstAddress = c.Address cntAaaE = 0 cntAaaNoE = 0 Do myRow = c.Row Set rng2 = ActiveSheet.Cells(myRow, 2) Set d = rng2.Find("exc", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) If Not d Is Nothing Then cntAaaE = cntAaaE + 1 Else cntAaaNoE = cntAaaNoE + 1 End If Set c = rng1.FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If MsgBox "cntAaaE=" & cntAaaE & ", cntAaaNoE=" & cntAaaNoE End Sub I expected it to print "cntAaaE=2, cntAaaNoE=1". I need to count lines that match "aaaa" in a 1st column AND having "exc" as a part of a value in a 2nd column. But unfortunately, rng1.FindNext(c) does not do what I want. Appreciate your help. "Greg Wilson" wrote in message ... The following code found all instances of the substring "excel" within the range A1:C10. The Selection and MsgBox code is for demo purposes only. I think what you are looking for is the FindNext method. Note that, here, "d" serves as the "After" argument to FindNext (i.e. FindNext(d)). In other words, find the next cell containing the substring, skipping the first cell. The code then sets d to this new cell and so forth. Also note that the FindNext method will form a nonterminating loop, finding the first cell again once it comes to the end of the range and repeating from there. That's why we record the address of the first instance (adr) and stop it at this point. Sub FindSubString() Dim d As Range Dim rng As Range Dim cdnName As String Dim adr As String cdnName = "excel" Set rng = Range("A1:C10") Set d = rng.Find(cdnName) If Not d Is Nothing Then d.Select MsgBox d.address adr = d.Address ' record address of first instance Do Set d = rng.FindNext(d) d.Select MsgBox d.Address Loop Until d.Address = adr ' stop when loops around to first instance End If End Sub "Vik" wrote: Hi All, I am new in Excel programming. I need to check if a cell contains a substring, something like "*excel*". First, I tried Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) Only it looks like .Find cannot be used more than once. I have a nested loop and used .Find in a wrapper loop. I need some alternative to .Find. Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
Correct the minor error in my code where the Like operator looks for "*ece*"
instead of "*exe*". Regards, Greg |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
Greg,
It works great and it is exactly what I need. On more thing. The method Like is case sensitive. If I need to ignore case, do I have to convert to lower case contents of a cell before? Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
chech if a cell contains a substring
Put the declaration "Option Compare Text" at the top of the module. This
should make it case insensitive. Regards, Greg "Vik" wrote: Greg, It works great and it is exactly what I need. On more thing. The method Like is case sensitive. If I need to ignore case, do I have to convert to lower case contents of a cell before? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
substring | Excel Discussion (Misc queries) | |||
Are contents of at least one cell in a group a substring of a dif | Excel Worksheet Functions | |||
Filter substring of a cell | Excel Discussion (Misc queries) | |||
chech if two or more cells = and return a value | Excel Discussion (Misc queries) | |||
Substring | Excel Discussion (Misc queries) |