我已經撰寫了一個 VBA 代碼來回圈遍歷特定值的范圍，選擇包含該值的單元格、緊鄰其上方的單元格和緊鄰其下方的單元格使用偏移量。問題是代碼選擇了單元格值的第一個實體為 1111 和兩個偏移量（選擇了 1111、緊接其上方的單元格和緊接其下方的單元格），但不是為其余的范圍內的指定值及其各自（緊接其上方和下方的單元格）偏移量。請協助
編碼：
Sub SelectMatchingCell()
Dim strInt As Integer
Dim rng As Range, c As Range, MyRng As Range, offsetCell1 As Range, offsetCell2 As Range
'Set range with values to be searched for matches
Set rng = ActiveSheet.Range("J3:J10555")
'Fill string variable with string of text to be matched
strInt = 1111
'Loop through each cell in range
For Each c In rng
'Check if cell value matches the string to be matched
If c.Value = strInt Then
'Check if this is the first match (new range hasn't been filled yet)
If MyRng Is Nothing Then
'Fill new range with cell
Set MyRng = c
Set offsetCell1 = MyRng.Cells.Offset(-1, 0)
Set offsetCell2 = MyRng.Cells.Offset(1, 0)
Else
'Join new matching cell together with previously found matches
Set MyRng = Application.Union(MyRng, c, offsetCell1, offsetCell2)
End If
End If
Next c
'Select entire row of each cell in new range
MyRng.Cells.Select
End Sub
uj5u.com熱心網友回復：
您只需在第一場比賽中設定 offsetCell1 和 offsetCell2。您還需要在 Else 陳述句中包含這兩行。
If c.Value = strInt Then
'Check if this is the first match (new range hasn't been filled yet)
If MyRng Is Nothing Then
'Fill new range with cell
Set offsetCell1 = c.Cells.Offset(-1, 0)
Set offsetCell2 = c.Cells.Offset(1, 0)
Set MyRng = Application.Union(c, offsetCell1, offsetCell2)
Else
'Join new matching cell together with previously found matches
Set offsetCell1 = c.Cells.Offset(-1, 0)
Set offsetCell2 = c.Cells.Offset(1, 0)
Set MyRng = Application.Union(MyRng, c, offsetCell1, offsetCell2)
End If
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/338658.html