Results 1 to 1 of 1

Allow Multiple or Single String Search Criteria Macro

Threaded View

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2002
    Posts
    15

    Allow Multiple or Single String Search Criteria Macro

    Hi all, I am editing a workbook that I found on this forum, and I am trying to create a macro button that will allow a person to select either 1 sub-category criteria or several (up to 10 would be perfect, but 23 would be ideal). I am fine with creating numerous buttons to do so if that would be necessary.

    I noticed while using the workbook that it will automatically populate only 7 rows in the Results field, so if there are less than 7 fields then there will be duplicates - if there are more then results will be missing. Can anyone help solve this? Below is some code that I have tried but it doesn't appear to be helping:


    Private Sub CommandButton1_Click()
    
    Dim i As Long, rData As Range, vItem(), j As Long
    
    Sheets("RESULTS").UsedRange.Offset(1).Clear
    
    With Sheets("FINAL_TABLE")
    .AutoFilterMode = False
    For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
    j = j + 1
    ReDim Preserve vItem(j)
    vItem(j) = Me.ListBox1.List(i)
    End If
    Next i
    For j = LBound(vItem) To UBound(vItem)
    .Range("A1").AutoFilter Field:=3, Criteria1:=vItem(j)
    With .AutoFilter.Range
    On Error Resume Next
    Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rData Is Nothing Then
    rData.Copy Sheets("RESULTS").Cells(Rows.Count, 1).End(xlUp)(2)
    End If
    End With
    .ShowAllData
    Next j
    End With
    
    Unload UserForm4
    
    End Sub
    Could anyone please help me out? Thank you so much in advance!
    Attached Files Attached Files
    Last edited by eurydice88; 12-22-2009 at 06:02 AM. Reason: Title change and worksheet edit

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1