背景
我有一个活动门票分配的电子表格。电子表格的每一行都有一个名称和分配的票数。
我需要更改电子表格,以便每个票证的每个名称在不同的行上重复一次,如下所示:
我有一个宏可以做到这一点,但它表现出奇怪的行为
问题
该宏不会循环遍历整个数据集。单步执行代码表明,尽管有意增加LastRow
,For 循环仅循环指定原始值的次数。的新值LastRow
在每次迭代结束时似乎都被忽略了。
这看起来特别奇怪,因为等效的 Do While 循环工作正常(请参阅下面使用 Do While 循环的工作代码)
问题
为什么会出现问题部分(上面)中描述的行为,以及为什么它与等效结构不一致?
For 循环宏
Sub InsertSurnames()
Dim LastRow As Long
Dim r As Long
Dim surname As String
Dim tickets As Integer
Dim surnameCol As Integer
Dim ticketCol As Integer
Dim targetCol As Integer
surnameCol = 1
ticketCol = 3
targetCol = 4
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For r = 1 To LastRow
surname = Cells(r, surnameCol).Value
tickets = Cells(r, ticketCol).Value
If (Not (Len(surname) = 0)) Then
Cells(r, targetCol).Value = surname
For x = 1 To tickets - 1
Cells(r + x, 1).EntireRow.Insert
Cells(r + x, targetCol).Value = surname
Next x
LastRow = LastRow + tickets - 1
End If
Next r
End Sub
Do While 循环宏
Sub InsertSurnames()
Dim LastRow As Long
Dim r As Long
Dim surname As String
Dim tickets As Integer
Dim surnameCol As Integer
Dim ticketCol As Integer
Dim targetCol As Integer
surnameCol = 1
ticketCol = 3
targetCol = 4
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
r = 1
Do While r <= LastRow
surname = Cells(r, surnameCol).Value
tickets = Cells(r, ticketCol).Value
If (Not (Len(surname) = 0)) Then
Cells(r, targetCol).Value = surname
For x = 1 To tickets - 1
Cells(r + x, 1).EntireRow.Insert
Cells(r + x, targetCol).Value = surname
Next x
LastRow = LastRow + tickets - 1
End If
r = r + 1
Loop
End Sub