Excel VBA。如何告诉宏键1到10键向下重复直到结束？

Currently trying to create a macro that fills in number 1 to 10 down a column and repeats until the end of my For statement. This is what I have in mind:

目前正在尝试创建一个在列中填充数字1到10的宏，并重复直到我的For语句结束。这就是我的想法：

With a click of a button the number will start at row 3 and count to 10. Once done it will repeat until it reaches the end. I previously done a variant this program which creates repeats of 1 for 10 rows, repeat 2 for the next 10 rows and so on like so:

只需单击一个按钮，该数字将从第3行开始并计数到10.一旦完成，它将重复直到它结束。我以前做了一个变种这个程序，它为10行创建1的重复，为接下来的10行重复2，依此类推：

Sub fill()

Dim ID
ID = 1
For c = 1 To 34        
    ActiveWorkbook.Sheets("Sheet1").Cells(c + 2, 1) = ID
    ActiveWorkbook.Sheets("Sheet1").Cells(c + 3, 1) = ID
    c = c + 1
    If (c Mod 10) = 0 Then
        ID = ID + 1
    End If

Next c

End Sub

If I want to convert it to counting 1-10 and then repeat, what needs to be changed. I would like to reuse this code with some modifications to the formula.

如果我想将其转换为1-10，然后重复，需要更改什么。我想重复使用此代码并对公式进行一些修改。

1 个解决方案

#1

Here is an example of a lazy way to do this.

这是一个懒惰的方法的例子。

Sub Macro1(offsetIn As Integer, colIn As Integer)

offset = offsetIn - 1
Column = colIn

For c = 1 To 34

  ActiveWorkbook.Sheets("Sheet1").Cells(c + offset, colIn) = c Mod 10

  If (c Mod 10 = 0) Then
     ActiveWorkbook.Sheets("Sheet1").Cells(c + offset, colIn) = 10
  End If

Next c

End Sub

As for getting this macro to trigger on click. You can do something like this: This code needs to go into your worksheet code. i.e. right click on sheet icon and click "view code".

至于让这个宏在点击时触发。你可以这样做：这段代码需要进入你的工作表代码。即右键单击工作表图标并单击“查看代码”。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rInt As Range
    Dim rCell As Range

    Set rInt = Intersect(Target, Range("A1:P312"))
    If Not rInt Is Nothing Then

        Call Macro1(rInt.Row, rInt.Column)

    End If
    Set rInt = Nothing
    Set rCell = Nothing
    Cancel = True
End Sub

EDIT: This should now work for any double click on any cell within the Range("A1:P312").

编辑：现在应该可以双击范围内的任何单元格（“A1：P312”）。

#1