Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

How do I insert wildcards into a vba search?

Here is a code that works for my first search.
strTmp = ""
strTmp = Filter(aLines, "Bldg:")(0)
Range("B7") = Trim(Split(strTmp, ":")(1))

Open in new window


I am trying to make another search to find a string that starts with a "W" and is 12 characters total in length. ExampleL WRGLED4K96KK
strTmp = ""
strTmp = Filter(aLines, "W???????????")(0)
Range("B7") = Trim(Split(strTmp, ":")(1))

Open in new window

0
kbay808
Asked:
kbay808
  • 6
  • 5
  • 3
  • +1
1 Solution
 
ProfessorJimJamCommented:
With two time double quotes like this

""*""
0
 
ProfessorJimJamCommented:
""W?????????""
0
 
kbay808Author Commented:
This is what have, but line 2 is causing the following error: "Expected: list separator or)"  The error goes away when is change the "" back to ", but nothing happens of course.  And is line 3 correct?  

strTmp = ""
strTmp = Filter(aLines, ""W?????????"")(0)
Range("C17") = strTmp

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ReneD100Commented:
I don't think you can use a wildcard in the array filter method - never seen it. If you look for WRT any value that contains WRT will be returned, so it's like the search match is already *WRT*.
If you want to filter on string length, maybe you can do something like this:
strTemp() = Filter(aLines, "WRT")
For Each strTmp In strTemp
    If Len(strTmp) = 12 Then
        'whatever you want to do with it
    End If
Next

Open in new window

0
 
kbay808Author Commented:
Is there another way that I can accomplish this task?  I need a way to search a cell and find a code.

Examples:
WRGLED4K96KK
W46DF6RE2FE6
WSDFGHH6FG55
0
 
ReneD100Commented:
Maybe you have to clarify your problem a bit more: are all these codes in ONE cell? Or each code in its own cell? All the cells located in the same column? Can you use a filter? Etc.
0
 
Glenn RayExcel VBA DeveloperCommented:
I understand your wanting to use the filter function, but why can you just test for a cell whose value begins with "W" and whose length is 12 characters?

If you're trying to find a 12-character substring that begins with "W" inside a longer string, then you could probably use regular expressions to test.
0
 
kbay808Author Commented:
I am trying to find a 12-character substring that begins with "W" inside a longer string.  Here are some examples of what could be in a cell.

Example 1
Name: John Doe
Phone 555-555-5555

Example 2
Code: WRGLED4K96KK
Name: John Doe
Phone 555-555-5555
W46DF6RE2FE6

Example 3
Requesting work to be completed on WSDFGHH6FG55 by COB today
0
 
ReneD100Commented:
So why first put it in an array? Just and idea:
    Dim ws As Worksheet
    Dim rSearch As Range
    Dim rFound As Range
    
    Set ws = Worksheets(1)
   
    Set rSearch = ws.UsedRange
    Set rFound = r.Find("w???????????")

Open in new window

You can use the Find method of a range to get what you want. You can loop until the range IsNothing() to find all matches
http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx
0
 
Glenn RayExcel VBA DeveloperCommented:
Unfortunately, this Find method
Set rFound = rSearch.Find("W???????????")

Open in new window

will return a positive result for any string of twelve characters, including spaces and punctuation, beginning with a "w",  For example:
Work on W1234567 was completed on January 14, 2014.
0
 
Glenn RayExcel VBA DeveloperCommented:
Here is code uses regular expressions to look for a pattern of twelve alphanumerica characters beginning with "W" inside cells in column A.  If it finds a match, it displays that match in column B.
Option Explicit
Sub Find_Matches()
    Dim objRegExp As Object
    Dim objMatches As Object
    Dim rng As Range
    Dim cl As Range
    
    Set rng = Range("A2", Range("A2").End(xlDown))
    
    Set objRegExp = CreateObject("vbscript.regexp")
    objRegExp.Global = False
    objRegExp.Pattern = "(W[a-z,0-9]{11})"
    objRegExp.ignorecase = True
    
    For Each cl In rng
        If objRegExp.Test(cl.Value) Then
            Set objMatches = objRegExp.Execute(cl.Value)
            cl.Offset(0, 1).Value = objMatches(0).submatches(0)
        Else
            cl.Offset(0, 1).Value = ""
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window


It only displays the first match, if there is one.  

For a great reference to regular expressions, see this article here in Experts Exchange:
Using Regular Expressions in Visual Basic for Applications and Visual Basic 6

Example file attached.

-Glenn
EE-FindSubstrings.xlsm
0
 
kbay808Author Commented:
Glenn Ray,

Your last script works the best, but it takes a long time to run for some reason.  It finds the first match within a second and then continues to run for an entire minute.  I tried it in a new workbook with the same result.  Is there a way to fix that?  Also, how can I get the result to be entered into a specific cell like D8?
0
 
Glenn RayExcel VBA DeveloperCommented:
I'm not sure about the speed issues; I only tested with a limited data set (seen in the example workbook), but I don't know any reason why it would slow down significantly.

As for the location of the results, I presume you mean you want the relative results in column D (ex., results on the same row as the source data).  If that's the case, then you'd change the cl.Offset(0, 1).Value command in lines 18 and 20  to cl.Offset(0, 3).Value

If you could post some more example data, I'll look into the performance issue.

-Glenn
0
 
kbay808Author Commented:
I figured it out how to make it faster.  Now it works great.  I just removed the below code.

 Else
            cl.Offset(0, 1).Value = ""

Open in new window

0
 
kbay808Author Commented:
Thanks for your help
0
 
Glenn RayExcel VBA DeveloperCommented:
Okay. I did that because any existing value was not overwritten and I wanted to make sure the cell was left blank if the source was changed and result was different.  You could just delete the entire "results" column and then run the macro instead.

Glad I could help.

-Glenn
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now