如何修改VBA代码实现全工作簿指定字符搜索而非仅单个工作表

核心修改点 #

• 新增全工作表遍历逻辑，自动跳过存储结果的Sheet2，避免重复搜索
• 搜索目标替换为匹配CGYSR-"##"格式的内容，使用通配符确保匹配规则准确
• 新增边界判断，避免匹配到的内容行号过小时，Offset取数出现下标越界错误

修改后完整代码 #

Option Explicit

Sub FindPriceTagInformation()
    Dim FirstAddress As String
    Dim Rng As Range
    Dim Rcount As Long
    Dim ws As Worksheet
    Dim NewSh As Worksheet

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set NewSh = Sheets("Sheet2")
    Rcount = 0
    
    ' 遍历当前工作簿所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 跳过结果存放表，避免重复搜索
        If ws.Name <> NewSh.Name Then
            With ws.Range("A1:ZZ300")
                ' 搜索符合 CGYSR-"两位数字" 格式的内容
                Set Rng = .Find(What:="CGYSR-""[0-9][0-9]""", _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                MatchWildcards:=True)
                
                If Not Rng Is Nothing Then
                    FirstAddress = Rng.Address
                    Do
                        ' 边界判断：避免行号太小导致Offset取数报错
                        If Rng.Row > 5 Then
                            Rcount = Rcount + 1
                            NewSh.Cells(Rcount, 3).Value = Rng.Value
                            NewSh.Cells(Rcount, 2).Value = Rng.Offset(-3, 0).Value
                            NewSh.Cells(Rcount, 1).Value = Rng.Offset(-5, 0).Value
                            ' 可按需添加代码存储匹配内容所属工作表名：NewSh.Cells(Rcount, 4).Value = ws.Name
                        End If
                        Set Rng = .FindNext(Rng)
                    Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
                End If
            End With
        End If
    Next ws

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

可选调整说明 #

如果不需要严格匹配数字，只要是CGYSR-"后面跟两个任意字符的规则，可将Find参数的What修改为"CGYSR-""??"""即可。如果需要调整搜索区域范围，修改ws.Range("A1:ZZ300")的区域参数即可。

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