DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008

    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!

    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
                'Paste row into Sheet2 in next row
                Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                'Move counter to next row
                LCopyToRow = LCopyToRow + 1
                'Go back to Sheet1 to continue searching
            End If
            LSearchRow = LSearchRow + 1
        'Position on cell A3
        Application.CutCopyMode = False
        MsgBox "All matching data has been copied."
        Exit Sub
        MsgBox "An error occurred."
    End Sub

  2. #2
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    Welcome to DevX

    You would want to use a wildcard search.

  3. #3
    Join Date
    Sep 2008
    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
    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
    Sterling Heights, Michigan
    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.

  6. #6
    Join Date
    Sep 2008

    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.

    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
    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
    Sterling Heights, Michigan
    Thanks for posting your find.

Similar Threads

  1. Wildcard sheet Excel Import??
    By Korse in forum VB Classic
    Replies: 2
    Last Post: 01-25-2008, 12:31 PM
  2. help regarding excel macro!!
    By Farooko in forum VB Classic
    Replies: 1
    Last Post: 04-27-2007, 01:16 PM
  3. Replies: 1
    Last Post: 01-02-2007, 08:58 AM
  4. Excel Macro Help Request!!!
    By TatesMommy in forum VB Classic
    Replies: 1
    Last Post: 10-16-2006, 10:31 AM
  5. Using Word commands through an Excel macro
    By Pass0 in forum VB Classic
    Replies: 1
    Last Post: 10-24-2005, 01: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
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.