通过excel上的搜索框一次过滤多个表 [英] Filtering multiple tables at once via a search box on excel

问题描述

我有一本13页的excel工作簿（一年每月一个，一张主页）和每张表中的员工统计表相同。员工统计表以相应的月份命名，并具有相同的列标题。
我在主页上有一个仪表板，其中有从其他表格拉出的图表。

I have an excel workbook with 13 sheets (one for each month in the year + a main sheet) and an identical table of employee stats in each sheet. The employee stat table is named after it's corresponding month and has identical column headers. I have a dashboard on the main sheet that has charts pulling from the other tables.

我想创建一个搜索框，允许您一次过滤其各自工作表中的所有表格

这是我无法想象的代码行。我试图使过滤器范围引用多个表。

Here is the line of code that I can't figure out. I am attempting to make the filter range refer to more than one table.

 Set DataRange = sheets.ListObjects("January""February""March").Range 

有没有办法编写搜索框来引用多个表在多页？我需要识别工作表名称和表名称。我不知道该怎么做

Is there any way to code the searchbox to refer to multiple tables in multiple sheets? I would need to identify the worksheet name, and the table name. I'm not sure how to do this

为了参考，1月是标题为Jan的表格。

For reference, January is the table in the sheet titled "Jan".

以下是我使用的完整代码：

Here is the full code I use:

 Sub SearchBox()
  Dim dict as Object
  Set dict = CreateObject("Scripting.Dictionary")

 Dim i as Long
 For 1 = 3 to 14
 Set dict(i) = Worksheets(i).ListObjects(1).Range

   Dim myButton As OptionButton
   Dim MyVal As Long
   Dim ButtonName As String
   Dim sht As Worksheet
   Dim myField As Long
   Dim DataRange As Range
   Dim mySearch As Variant

  'Load Sheet into A Variable
   Set sht = ActiveSheet

 'Unfilter Data (if necessary)
  On Error Resume Next
  sht.ShowAllData
  On Error GoTo 0

 'Filtered Data Range (include column heading cells)

   dict(i).Autofilter_

   Field:=myField,_
   Criteria1:="=*" & mySearch & "*", _
   Operator:=xlAnd

   Next

  'Retrieve User's Search Input
   mySearch = sht.Shapes("StaffLookUp").TextFrame.Characters.Text 'Control Form


   'Loop Through Option Buttons
    For Each myButton In ActiveSheet.OptionButtons
    If myButton.Value = 1 Then
    ButtonName = myButton.Text
    Exit For
      End If
     Next myButton

  'Determine Filter Field
   On Error GoTo HeadingNotFound
   myField = Application.WorksheetFunction.Match(ButtonName,          DataRange.Rows(1), 0)
  On Error GoTo 0

  'Filter Data
   DataRange.AutoFilter _
     Field:=myField, _
     Criteria1:="=*" & mySearch & "*", _
     Operator:=xlAnd

  'Clear Search Field
   sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form


Exit Sub

'ERROR HANDLERS
HeadingNotFound:
 MsgBox "The column heading [" & ButtonName & "] was not found in cells " &        DataRange.Rows(1).Address & ". " & _
 vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"

 End Sub

推荐答案

你应该能够使用 Range（Name）引用ListObjects。

You should be able to reference the ListObjects by name using Range(Name).

我将过滤器过程提取到自己的子我还添加了一个可选参数ClearFilters。这将为您提供堆栈过滤器的选项。

I extracted the filter process into it's own sub. I also added an optional parameter ClearFilters. This will give you the option to stack the filters.

Sub ApplyFilters()
    Dim FieldName As String, mySearch As Variant
    Dim myButton As OptionButton
    Dim m As Integer

    For Each myButton In ActiveSheet.OptionButtons
        If myButton.Value = 1 Then
            FieldName = myButton.Text
            Exit For
        End If
    Next myButton

    mySearch = ActiveSheet.Shapes("StaffLookUp").TextFrame.Characters.Text    'Control Form
    mySearch = "=*" & mySearch & "*"

    For m = 1 To 12

       FilterTable MonthName(m), FieldName, mySearch, True

    Next

End Sub


Sub FilterTable(TableName As String, FieldName As String, mySearch As Variant, Optional ClearFilters As Boolean = False)
    Dim DataRange As Range, FilterColumn As Integer

    On Error Resume Next
    Set DataRange = Range(TableName)
    On Error GoTo 0

    If DataRange Is Nothing Then
        MsgBox TableName & " not found"
        Exit Sub
    End If

    If ClearFilters Then
        On Error Resume Next
        DataRange.Worksheet.ShowAllData
        On Error GoTo 0
    End If

    On Error Resume Next
    FilterColumn = DataRange.ListObject.ListColumns(FieldName).Index
    On Error GoTo 0

    If FilterColumn = 0 Then
        MsgBox TableName & ": " & FieldName & " not found"
        Exit Sub
    End If

    DataRange.AutoFilter _
            Field:=FilterColumn, _
            Criteria1:=mySearch, _
            Operator:=xlAnd

End Sub

这篇关于通过excel上的搜索框一次过滤多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！