如何在Excel的每个工作表中搜索特定的列

I have an excel document with over 50 worksheets all with a similar naming convention. As this will be so unfriendly for users to navigate to, I wrote a VBA macro which creates a worksheet called summary with the list of all the worksheets hyperlinked in a tabular form with Sheet A B and C as the Column and Sheet 1 and 2 as rows.

我有一个超过50个工作表的excel文档，它们都有类似的命名约定。由于这对用户来说是非常不友好的，所以我编写了一个VBA宏，它创建了一个名为summary的工作表，该工作表以表a B和C作为列，以表1和表2作为行，以表形式将所有工作表的列表进行超链接。

Now I am trying to go through each row of a specific column in Sheet 1 and Sheet 2 and look for any reference to SheetB, SheetC and SheetD and for each reference found and I want to mark that creating a matrix.

现在我试着遍历表1和表2中的每一行，查找任何关于SheetB、SheetC和SheetD的引用，并查找每个引用，我想标记创建一个矩阵。

I am not sure how to achieve this. Any assistance will be much appreciated.

我不知道该如何做到这一点。如有任何帮助，我们将不胜感激。

I have managed to search Sheet 1 and 2 for any reference to SheetB as shown below but I am not sure how to update the corresponding cell in my summary sheet.

我已经成功地搜索了第1和2页，以获得对SheetB的任何引用，但我不确定如何在总结表中更新相应的单元格。

Function findWord(word As String, wSheet As String) As Boolean

    Dim LastRow As Long
    Dim i As Long
    LastRow = Worksheets(wSheet).Cells(Rows.Count, "D").End(xlUp).Row

    For i = LastRow To 1 Step -1
       If Worksheets(wSheet).Range("D" & i).Value = word Then
          findWord = True
          Exit Function
       End If
    Next i
End Function


For Each wsSheet In wbBook.Worksheets
    If (wsSheet.Name <> wsActive.Name) And (Left(wsSheet.Name, 4) <>   "fact") Then
    For i = 2 To lastColumn
         MsgBox wsSheet.Name
       If findWord(columnNames(counter2), wsSheet.Name) Then
          'Update summary sheet
       End If
       counter = counter2 + 1
    Next i

End If
Next wsSheet

1 个解决方案

#1

If the result in "Summary sheet" you are looking for is similar to this :
如何在Excel的每个工作表中搜索特定的列

如果您所寻找的“汇总表”的结果与以下类似:

Then you can use something like this (read the comments inside the code for explanations)

然后您可以使用类似的东西(请阅读代码中的注释以获得解释)

Sub MarkReferencesToSheets()

    Dim wsSummary As Worksheet 'sheet with summary table matrix
    Dim wsSheetRow As Worksheet 'sheets in which we will search references to other sheets
    Dim strSheetColumnName As String 'name of the reference we are looking for
    Dim intSheetRow As Integer 'for loop purposes
    Dim intSheetColumn As Integer 'for loop purposes

    Set wsSummary = Sheets("Summary")

    For intSheetRow = 2 To 3 'change to suit; headers for rows in summary sheet
        Set wsSheetRow = Worksheets(wsSummary.Cells(intSheetRow, 1).Value)
        For intSheetColumn = 2 To 4 'change to suit; headers for columns in summary sheet
            strSheetColumnName = wsSummary.Cells(1, intSheetColumn) 'name of sheet we are looking for
            If Not wsSheetRow.Columns(4).Find(strSheetColumnName) Is Nothing Then 'look only in column "D", or 4
                wsSummary.Cells(intSheetRow, intSheetColumn) = "X" ' if we found it, mark it
            Else
                'if you want something else in the cell when reference is not found, put it here
            End If
        Next intSheetColumn
    Next intSheetRow

End Sub

#1