能否通过VBA代码标记每行Result列中CHECK所属的工作表？

实现思路 #

• 遍历主表每行数据（跳过表头）
• 对每行名称，逐个检查表头对应的工作表
• 若工作表中找到该名称且对应单元格含“CHECK”，记录工作表名
• 将符合条件的工作表名用“和”连接，写入该行“Result”列

VBA代码实现 #

Sub MarkCheckSheets()
    Dim mainWS As Worksheet
    Dim targetWS As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long
    Dim j As Long
    Dim nameToFind As String
    Dim resultStr As String
    Dim foundCell As Range
    
    ' 指定主表，替换为你的主表名称
    Set mainWS = ThisWorkbook.Worksheets("Main")
    lastRow = mainWS.Cells(mainWS.Rows.Count, "A").End(xlUp).Row
    lastCol = mainWS.Cells(1, mainWS.Columns.Count).End(xlToLeft).Column
    
    ' 遍历每一行数据
    For i = 2 To lastRow
        resultStr = ""
        nameToFind = mainWS.Cells(i, 1).Value ' 假设名称在A列，按需修改
        
        ' 遍历表头对应的工作表
        For j = 2 To lastCol
            If mainWS.Cells(1, j).Value <> "Result" Then
                On Error Resume Next
                Set targetWS = ThisWorkbook.Worksheets(mainWS.Cells(1, j).Value)
                On Error GoTo 0
                
                If Not targetWS Is Nothing Then
                    ' 在目标工作表A列查找名称，按需修改列
                    Set foundCell = targetWS.Columns("A").Find(What:=nameToFind, LookIn:=xlValues, LookAt:=xlWhole)
                    
                    If Not foundCell Is Nothing Then
                        ' 检查对应B列是否含CHECK，按需修改列偏移量
                        If InStr(1, foundCell.Offset(0, 1).Value, "CHECK", vbTextCompare) > 0 Then
                            If resultStr = "" Then
                                resultStr = targetWS.Name
                            Else
                                resultStr = resultStr & "和" & targetWS.Name
                            End If
                        End If
                    End If
                    
                    Set targetWS = Nothing
                End If
            End If
        Next j
        
        ' 写入Result列（默认最后一列，按需修改）
        mainWS.Cells(i, lastCol).Value = resultStr
    Next i
    
    MsgBox "标注完成！", vbInformation
End Sub

注意事项 #

• 替换代码中"Main"为你的实际主表名称
• 确认名称所在列（代码中为A列）、CHECK所在列（代码中为B列）与表格匹配
• 若"Result"列不在最后一列，需调整代码中跳过Result列的判断逻辑
• 运行前备份文件，避免数据风险

内容的提问来源于stack exchange，提问作者Novia