excel - 在某些单元格中循环的宏

Question

我正在将某些值从 Sheet3 复制到 Sheet2 并且我有一个循环，问题是我只想在单元格中循环。基本上，如果我在 Sheet2 的 D 列中有值 1、2、3、4 等，我只想在包含数字“1”的 H 列行中循环。到目前为止，这是我的代码。

Sub rand_v2()

Dim RNG1 As Range, RNG2 As Range, r As Range, p As Range, d As Collection, N As Long, MLRow As Long, x As Long

Set RNG1 = Sheet3.Range("A2:A500")
MLRow = Sheet2.Range("B" & Rows.Count).End(xlUp).Row


For x = 4 To MLRow
    If Sheet2.Cells(x, 8).Value = vbNullString Then
        Set d = New Collection
        For Each r In RNG1
            If r.Value <> vbNullString And Sheet3.Cells(r.Row, 4).Value = Sheet2.Cells(x, 2).Value And r.Value <> Sheet2.Cells(x - 1, 8).Value And r.Value <> Sheet2.Cells(x - 2, 8).Value And r.Value <> Sheet2.Cells(x - 3, 8).Value Then
                d.Add r
                N = Application.WorksheetFunction.RandBetween(1, d.Count)
                d.Item(N).Copy
                Sheet2.Cells(x, 8).PasteSpecial xlPasteValues
            End If
        Next r
        Set d = Nothing
    End If

Next x
End Sub

标签： excelvba