Excel/VBA 2010 - searching for wild cardsClosed

Question

Hello, 

I'm wondering if it is possible to write a code that will search for wild cards. For example: we have a pattern of numbers that come up in the format 111-11-111. Is there a way to write a code that will look for ???-??-???

Also, another aspect is I'm wondering if we can search for single words. In this case different names which occur in a cell by itself without any other characters. What is happening is we have names and addresses on our spreadsheet but if there is more than one person addressed then the name of the second person appears in the row below in a cell by itself. We want to identify and delete the row with the name by itself. Here's an example of the text: 

MR & MRS C & S Johnson
PO BOX 1000
WANAGNUI 1342

MR R PEARCE & MS J
SMITH
40C TRIDENT PLACE
OAKURA 4314

MR J DUNCAN & MS J
JONES
9 BULL AVENUE
KILBIRNIE
WELLINGTON 6012

rizvisa1 · Answer

if you use FIND in vba, you can use the wild card of * or ? as case may be

I think that answers both of your questions

rizvisa1 · Answer

You find ***-**-*** is problematic. I think you should use "?" instead of "*" as ? is place holder of one character and * is saying there can be any thing of any length

The FIND function does look at the sheet and give you the cell where it finds the match or returns NOTHING if no match is found. Below is a function that I have been using to location a row or a column within the defined range to look up a value. This function will return 0 if no match is found or will return the row number (default) or the column number (if bFindRow =false). 

You can use the function to locate the row. Once row is located than you can see whats in column C of that row and do the needful


Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                
   'find the first/last row/column  within a range for a specific string
   
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
      
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
      
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
      
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

whitepanther · Answer

Thank you for that... I'm sorry but I really am very much a novice at this. I copied the coding you gave above into a module and tried to step through it to get an idea of how it works so I can try figure out what I need to adapt but I can't even get it to run!

Are you able to elaborate your example for me a bit more?

I'm afraid I'm also not sure how to write the second part once the specified row is found i.e. how to tell it to look at column C of that row to determine if there is something in that cell or not - and what action to take as a consequence.

Excel/VBA 2010 - searching for wild cards

3 responses

Subscribe To Our Newsletter!