Wildcard in Excel Macro


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Wildcard in Excel Macro

  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Wildcard in Excel Macro

    Hello all,

    I have a large sports-products inventory that I have to categorize based on teams. The inventory is in one Excel sheet, complete with product descriptions, and I'd like to run wildcard searches (ex: 49ers, Bears, Red Wings) so I can copy team products into another sheet where I can work with the results.

    I found the following code, but it wants exact matches. How can I expand this macro to act more like a wildcard search? The macro I'm trying to make would look at a product description like this, San Francisco 49ers Door Mat, see that it contains "49ers" and copy the row over to Sheet2. The code below accepts user input for the search string and copies rows successfully, but it doesn't do wildcards.

    Thanks in advance for any help!

    Code:
    Sub SearchForString()
    
        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer
        Dim LSearchValue As String
        
        On Error GoTo Err_Execute
        
        LSearchValue = InputBox("Please enter a value to search for.", "Enter value")
        
        'Start search in row 4
        LSearchRow = 4
        
        'Start copying data to row 2 in Sheet2 (row counter variable)
        LCopyToRow = 2
        
        While Len(Range("A" & CStr(LSearchRow)).Value) > 0
            
            'If value in column E = LSearchValue, copy entire row to Sheet2
            If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then
                
                'Select row in Sheet1 to copy
                Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
                Selection.Copy
                
                'Paste row into Sheet2 in next row
                Sheets("Sheet2").Select
                Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                ActiveSheet.Paste
                
                'Move counter to next row
                LCopyToRow = LCopyToRow + 1
                
                'Go back to Sheet1 to continue searching
                Sheets("Sheet1").Select
                
            End If
            
            LSearchRow = LSearchRow + 1
            
        Wend
        
        'Position on cell A3
        Application.CutCopyMode = False
        Range("A3").Select
        
        MsgBox "All matching data has been copied."
        
        Exit Sub
        
    Err_Execute:
        MsgBox "An error occurred."
        
    End Sub

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    You would want to use a wildcard search.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Sep 2008
    Posts
    6
    Quote Originally Posted by Hack
    Welcome to DevX

    You would want to use a wildcard search.
    Thanks for your response!

    If I'm not mistaken, the link you provided would return results for cells with asterisks or question mark characters. I'm not looking for cells with * or ?, but rather trying to run a search that would look for cells that have words that match my search criteria.

    For example, I have a column that lists product descriptions. Team names are found somewhere in the description. I'm trying to make a macro that will ask for the search criteria with an input box, then scan the Excel sheet within a specific column and find any cells that match the search string.

    The macro I have works in that it accept user input and copies rows to another sheet. The problem is that I have to input exact product descriptions to get it to work. I want to be able to enter one element that I know will show up in the description (i.e. team names). Looking at the macro, everything works except the search feature.

    Thanks for your help.

  4. #4
    Join Date
    Sep 2008
    Posts
    6
    Quote Originally Posted by Hack
    Welcome to DevX

    You would want to use a wildcard search.
    I think I misunderstood. Looking at the code, I can see that it's finding those wildcard characters anywhere in the cells. That's along the lines of what I need to do, except I need it to find text somewhere within cells.

    I'll keep working with it. I think I've got it. Thanks again for your help!

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    You should be able to refine it to meet your needs.

    If not, come back and post what you have got and what it is, or is not, doing.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Sep 2008
    Posts
    6

    Smile Found my solution

    Thanks to DevX and Hack for the help!

    I didn't have enough expertise to tweak the code myself, but I found this posting, http://www.access-programmers.co.uk/.../t-112628.html, and made a few modifications for my needs.

    Hope this helps anyone else looking to use an input box to wildcat search a column and transfer the corresponding rows to another sheet.

    Code:
    Sub TeamFind()
    Dim intS As Integer
    Dim rngC As Range
    Dim strToFind As String, FirstAddress As String
    Dim wSht As Worksheet
    
    Application.ScreenUpdating = False
    
    intS = 1
    'This step assumes that you have a worksheet named
    'Search Results.
    Set wSht = Worksheets("Sheet2")
    strToFind = InputBox("Enter the team you want to find")
    
    'Change this range to suit your own needs.
    With ActiveSheet.Range("F2:F13254")
    Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
    If Not rngC Is Nothing Then
    FirstAddress = rngC.Address
    Do
    rngC.EntireRow.Copy wSht.Cells(intS, 1)
    intS = intS + 1
    Set rngC = .FindNext(rngC)
    Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
    End If
    End With
    
    End Sub

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Thanks for posting your find.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Wildcard sheet Excel Import??
    By Korse in forum VB Classic
    Replies: 2
    Last Post: 01-25-2008, 01:31 PM
  2. help regarding excel macro!!
    By Farooko in forum VB Classic
    Replies: 1
    Last Post: 04-27-2007, 02:16 PM
  3. Replies: 1
    Last Post: 01-02-2007, 09:58 AM
  4. Excel Macro Help Request!!!
    By TatesMommy in forum VB Classic
    Replies: 1
    Last Post: 10-16-2006, 11:31 AM
  5. Using Word commands through an Excel macro
    By Pass0 in forum VB Classic
    Replies: 1
    Last Post: 10-24-2005, 02:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center