VBA Excel 嵌套 For 循环（在列中写下数字）

VBA Excel Nested For Loop (write numbers down column)

提问by matt

Nested loop sort of works. The problem - I can't figure out how to make the loop move on to the next block of cells (B11:B20); it's simply rewriting values in to range(B1:B10). My thoughts are that I may not need a nested loop but I can't wrap my head around a solution.

嵌套循环排序的作品。问题 - 我不知道如何让循环移动到下一个单元格块 (B11:B20)；它只是将值重写为范围（B1：B10）。我的想法是我可能不需要嵌套循环，但我无法解决解决方案。

Sub insertNum()

Dim sheetOne As Worksheet
Set sheetOne = Worksheets("Sheet1")

Dim i, j As Long
For i = 1 To sheetOne.UsedRange.Rows.Count
    For j = 1 To 10
        Select Case j
            Case 1 To 5
            sheetOne.Cells(j, 2).Value = "2"
            Case 6 To 10
            sheetOne.Cells(j, 2).Value = "3"
         End Select
    Next j
Next i
End Sub

采纳答案by Joe Laviano

This does what you want, I think. I tested it and it just switches between 2 and 3 every 5 rows.

这就是你想要的，我想。我测试了它，它只是每 5 行在 2 和 3 之间切换。

Sub insertNum()

Dim sheetOne As Worksheet
Set sheetOne = Worksheets("Sheet1")

Dim i As Long, j As Long
For i = 1 To sheetOne.UsedRange.Rows.Count Step 10
For j = 1 To 10
    Select Case j
        Case 1 To 5
        sheetOne.Cells(i + j - 1, 2).Value = "2"
        Case 6 To 10
        sheetOne.Cells(i + j - 1, 2).Value = "3"
     End Select
Next j
Next i
End Sub

回答by user2140261

You can accomplish this without using any loops in your code, Increasing the time it takes to execute by about 400% in this case by using the following code:

您可以在代码中不使用任何循环的情况下完成此操作，在这种情况下，通过使用以下代码将执行时间增加约 400%：

Sub insertNumNoLoopSample()
With [B1:B50000]
    .FormulaR1C1 = _
        "=IF(ISNA(MATCH(RIGHT(ROW(),1), {""1"",""2"",""3"",""4"",""5""},0)),3,2)"
    .Value = .Value
End With
End Sub

Tested with a range of B1:B50000 running each code (other answer and this) my code took on average 0.41 seconds to execute while the other answer took on average 2.1 seconds to execute.

测试范围为 B1:B50000 运行每个代码（其他答案和这个），我的代码平均执行时间为 0.41 秒，而另一个答案平均执行时间为 2.1 秒。

You can use the code as is, or modify the With line and add in the range you wish to run the code on, if you want to use the used range instead (this is not recommended) then you could replace

您可以按原样使用代码，或修改 With 行并添加您希望运行代码的范围，如果您想改用使用的范围（不推荐这样做），那么您可以替换

With [B1:B50000]

With [B1:B50000]

and use the below instead.

并使用下面的代替。

With ActiveSheet.UsedRange.Columns("B")

With ActiveSheet.UsedRange.Columns("B")

The best way to get the last used row instead of using used range if you don't know what the last row will be, or if it will change will be to use the following:

如果您不知道最后一行是什么，或者它是否会发生变化，那么获取最后使用的行而不是使用范围的最佳方法是使用以下内容：

Sub NoLoopSample()
Dim lastRow As Long
'Replace The A with the Column of your data that 
'will the longest amount of data.
lastRow = Range("A" & Rows.Count).End(xlUp).Row 

With Range("B1:B" & lastRow)
    .FormulaR1C1 = _
        "=IF(ISNA(MATCH(RIGHT(ROW(),1), {""1"",""2"",""3"",""4"",""5""},0)),3,2)"
    .Value = .Value
End With
End Sub

回答by Recipe

Shouldn't this be:

这不应该是：

Sub insertNum()

Dim sheetOne As Worksheet
Set sheetOne = Worksheets("Sheet1")

Dim i, j As Long
For i = 1 To sheetOne.UsedRange.Rows.Count
    For j = 1 To 10
        Select Case j
            Case 1 To 5
            sheetOne.Cells(j, i).Value = "2"
            Case 6 To 10
            sheetOne.Cells(j, i).Value = "3"
        End Select
    Next j
Next i
End Sub

It seems to me that you are hard-coded assigning the row (2) into your loop. I take it that i is the row looper, but you never use that value to assign your cell.

在我看来，您正在硬编码将行 (2) 分配到您的循环中。我认为 i 是行循环器，但您从不使用该值来分配您的单元格。