我有一个Excel文档,其中包含50多个工作表,所有工作表均具有类似的命名约定。由于这对于用户来说非常不友好,因此我编写了一个VBA宏,该宏创建了一个名为summary的工作表,其中所有工作表的列表以表格形式超链接,其中Sheet AB和C作为列,Sheet 1和2作为行。
现在,我尝试遍历工作表1和工作表2中特定列的每一行,并查找对SheetB,SheetC和SheetD的任何引用,以及找到的每个引用,我想标记一下创建矩阵。
我不确定如何实现这一目标。任何帮助将不胜感激。
我已经设法在工作表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
如果您要查找的“摘要表”中的结果类似于以下内容:
然后,您可以使用类似这样的内容(阅读代码内的注释以获取解释)
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句