VBA遍历Range查找指定值时Offset仅首个匹配生效问题求助

• 核心问题：原有代码中offsetCell1、offsetCell2仅在第一次匹配到1111时完成赋值，后续所有匹配项加入选区时，复用的都是第一个匹配单元格的上下相邻单元格，不会重新计算当前匹配单元格的相邻区域，导致后续匹配项对应的上下行没有被加入选区。
• 隐藏隐患：如果1111出现在搜索区域的首行（J3）或末行（J10555），偏移操作会超出表格范围，触发运行时错误；如果搜索区域内没有匹配值，最后的Select操作也会报错。

Sub SelectMatchingCell()
    Dim targetVal As Integer
    Dim rng As Range, c As Range, MyRng As Range, offsetCell1 As Range, offsetCell2 As Range
 
    ' 设置搜索区域
    Set rng = ActiveSheet.Range("J3:J10555")
    ' 设置要匹配的目标值
    targetVal = 1111
 
    ' 遍历搜索区域内的每一个单元格
    For Each c In rng
        If c.Value = targetVal Then
            ' 每次匹配到目标值时，单独计算当前单元格的上下相邻单元格
            Set offsetCell1 = Nothing
            Set offsetCell2 = Nothing
            ' 避免首行向上偏移越界
            If c.Row > rng.Row Then
                Set offsetCell1 = c.Offset(-1, 0)
            End If
            ' 避免末行向下偏移越界
            If c.Row < (rng.Row + rng.Rows.Count - 1) Then
                Set offsetCell2 = c.Offset(1, 0)
            End If
            
            ' 将当前匹配单元格及有效相邻单元格加入选区
            If MyRng Is Nothing Then
                Set MyRng = c
                If Not offsetCell1 Is Nothing Then Set MyRng = Application.Union(MyRng, offsetCell1)
                If Not offsetCell2 Is Nothing Then Set MyRng = Application.Union(MyRng, offsetCell2)
            Else
                Set MyRng = Application.Union(MyRng, c)
                If Not offsetCell1 Is Nothing Then Set MyRng = Application.Union(MyRng, offsetCell1)
                If Not offsetCell2 Is Nothing Then Set MyRng = Application.Union(MyRng, offsetCell2)
            End If
        End If
    Next c
 
    ' 存在匹配结果时选中对应区域
    If Not MyRng Is Nothing Then MyRng.Select
End Sub

1. 把偏移单元格的计算逻辑移到匹配判断块内部，每次匹配到目标值就重新计算当前单元格对应的上下相邻单元格，不再复用旧值
2. 增加偏移越界判断，匹配单元格在搜索区域首尾行时不会抛出错误
3. 增加空值判断，无匹配结果时不会触发Select操作的报错
4. 调整变量命名，将易歧义的strInt改为targetVal，可读性更高

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