大家好(第一次发帖,请耐心等待,哈哈),我编写了一个宏,它完全可以满足我的需要,只是我不知道如何让宏在 Sheet1 的另一个单元格中启动。它目前在单元格 B3 中发布我需要的信息,但我希望它从单元格 B10 开始。有什么想法吗?
Sub Button1_Click()
Sheets("Sheet1").Range("B2:B600").ClearContents
For i = 1 To 30
generate:
RowNum = Application.RoundUp(Rnd() * 88, 0)
If Application.CountIf(Sheets("Sheet1").[B:B], _
Sheets("Sheet2").Cells(RowNum, "A")) = 0 Then
Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = _
Sheets("Sheet2").Cells(RowNum, "A").Value
Else
GoTo generate
End If
Next i
End Sub
答案1
用这个
Option Explicit
Public Sub Button1_Click()
Const RNG = "B2:B600" '<--- Range on Sheet1 --------------------
Const OFFSET_ROW = 2
Const START_ROW = 10 '<--- Start row --------------------------
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Dim r As Long, ws2val As String
ws1.Range(RNG).ClearContents
For r = START_ROW To 30 * OFFSET_ROW + START_ROW - 1 Step OFFSET_ROW
ws2val = ws2.Cells(Application.RoundUp(Rnd() * 88, 0), "A").Value
If Application.CountIf(ws1.Range(RNG), ws2val) = 0 Then
ws1.Range("B" & r).Value = ws2val
Else
r = r - OFFSET_ROW
End If
Next
End Sub
测试数据工作表2,结果为工作表1
(按照您的预期,输出从第 10 行开始)