Solved

How do I insert wildcards into a vba search?

Posted on 2014-10-29
16
211 Views
Last Modified: 2014-10-29
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
Comment
Question by:kbay808
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40410123
With two time double quotes like this

""*""
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40410125
""W?????????""
0
 

Author Comment

by:kbay808
ID: 40410149
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40410290
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
 

Author Comment

by:kbay808
ID: 40411304
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40411338
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40411446
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
 

Author Comment

by:kbay808
ID: 40411478
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Expert Comment

by:ReneD100
ID: 40411572
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40411835
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40412024
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
 

Author Comment

by:kbay808
ID: 40412198
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40412442
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
 

Author Comment

by:kbay808
ID: 40412483
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
 

Author Closing Comment

by:kbay808
ID: 40412485
Thanks for your help
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40412495
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now