时间戳

首页 > 解决方案 > 如何搜索多个工作表？

excel - 如何搜索多个工作表？

问题描述

我在 Worksheet2 中搜索任何文本并在 ListBox1 中显示结果。

Private Sub SearchButton_Click()

    'ListBox1.Clear
    ListBox1.RowSource = ""
    ListBox1.ColumnHeads = False

    'listbox column headers
    Me.ListBox1.AddItem
    For A = 1 To 8
        Me.ListBox1.List(0, A - 1) = Sheet2.Cells(1, A)
    Next A
    Me.ListBox1.Selected(0) = True

    'Populating listbox from search
    Dim i As Long

    For i = 2 To Sheet2.Range("A100000").End(xlUp).Offset(1, 0).Row
        For j = 1 To 8
            H = Application.WorksheetFunction.CountIf(Sheet2.Range("A" & i, "H" & i), Sheet2.Cells(i, j))
            If H = 1 And LCase(Sheet2.Cells(i, j)) = LCase(Me.TextBox2) Or H = 1 And _
                Sheet2.Cells(i, j) = Val(Me.TextBox2) Then
                    Me.ListBox1.AddItem
                    For X = 1 To 8
                        Me.ListBox1.List(ListBox1.ListCount - 1, X - 1) = Sheet2.Cells(i, X)
                    Next X
            End If
       Next j
    Next i

End Sub

我想搜索多个工作表，但不知道如何在不完全更改代码的情况下实现这一目标。

标签： excelvba

解决方案

Sheet2如果要查看多张纸，您将不得不更改参考。没有办法解决这个问题。但是，它将使您的代码更加灵活。从这样做开始：

Private Sub SearchButton_Click()

'ListBox1.Clear
    ListBox1.RowSource = ""
    ListBox1.ColumnHeads = False

'listbox column headers
 Me.ListBox1.AddItem
 For A = 1 To 8
 Me.ListBox1.List(0, A - 1) = Sheet2.Cells(1, A)
 Next A
 Me.ListBox1.Selected(0) = True

Dim ws As Worksheet             'This is the new line of code where you define your worksheet
Set ws = ActiveWorkbook.Sheet2  'Replace all references below to Sheet2 with this

 'Populating listbox from search
 Dim i As Long

 For i = 2 To ws.Range("A100000").End(xlUp).Offset(1, 0).Row
 For j = 1 To 8
 H = Application.WorksheetFunction.CountIf(ws.Range("A" & i, "H" & i), Sheet2.Cells(i, j))
 If H = 1 And LCase(Sheet2.Cells(i, j)) = LCase(Me.TextBox2) Or H = 1 And _
 ws.Cells(i, j) = Val(Me.TextBox2) Then
 Me.ListBox1.AddItem
 For X = 1 To 8
 Me.ListBox1.List(ListBox1.ListCount - 1, X - 1) = Sheet2.Cells(i, X)
 Next X
 End If
 Next j
 Next i
 End Sub

现在您已经概括了您的 Sub，您可以修改 的值ws以尽可能多地重复代码。如果它是工作簿中的每张工作表，则可以使用For Each循环，例如

For Each ws In ActiveWorkbook
    'All your code for the ws here
Next ws

或者，您可以预先在数组中定义工作表。

Dim SheetList(0 to 2) As String
Dim k As Integer

SheetList(0) = "Sheet 2 Name"
SheetList(1) = "Sheet 4 Name"
SheetList(2) = "Sheet 3 Name"
SheetList(3) = "Sheet 6 Name"

For k = LBound(SheetList) To UBound(SheetList)
    ws = ActiveWorkbook.Sheets(SheetList(k))
    'The rest of your code from above
Next k

您没有在问题中指定什么样的表格有多少，或者它们是如何组织的。但是，这些选项应该足以让您到达您想要去的地方。

推荐阅读