我有两个工作表。表1（客户跟踪器-OC）有一列，每行包含是或否。如果值为是，则右侧的两个相邻单元格中有两个值。
我想为在sheet1的yes/no列中循环通过每个单元格的每个宏创建一个do while/，并且在值为yes的情况下，在空白工作表2中创建一个新行，其中两个相邻的值来自工作表1。
即
表1
A B C
1 YES 4 6
2 NO
3 YES 91 42
工作表2（“合同”）中的预期结果
A B
1 4 6
2 91 42
我有下面的代码，但它因“溢出”而出错。
Sub ForEach_Loop()
Dim TrackerLastRow As Integer
Dim TrackerCurrentRow As Integer
Dim ContractLastRow As Integer
Dim ContractCurrentRow As Integer
TrackerLastRow = Worksheets("Customer Tracker - OC").Range("T1").End(xlDown).Row
TrackerCurrentRow = 1
ContractLastRow = Worksheets("Contracts").Range("A1").End(xlDown).Row
ContractCurrentRow = 1
Do While TrackerCurrentRow <= TrackerLastRow
If Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Value = "Yes" Then
Worksheets("Contracts").Range("A" & ContractCurrentRow) = Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Offset(, 1).Value
Worksheets("Contracts").Range("B" & ContractCurrentRow) = Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Offset(, 2).Value
TrackerLastRow = TrackerLastRow + 1
TrackerCurrentRow = TrackerCurrentRow + 1
ContractLastRow = ContractLastRow + 1
ContractCurrentRow = ContractCurrentRow + 1
End If
TrackerCurrentRow = TrackerCurrentRow + 1
ContractCurrentRow = ContractCurrentRow + 1
Loop
End Sub
任何帮助都将不胜感激
您似乎过度增加了var并过度扩展了整数类型var的功能限制。
Option Explicit
Sub ForEach_Loop()
Dim TrackerLastRow As Long, TrackerCurrentRow As Long
Dim ContractLastRow As Long, ContractCurrentRow As Long
With Worksheets("Contracts")
ContractLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ContractCurrentRow = 1
End With
With Worksheets("Customer Tracker - OC")
TrackerLastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
TrackerCurrentRow = 1
Do While TrackerCurrentRow <= TrackerLastRow
If .Cells(TrackerCurrentRow, "T").Value = "Yes" Then
Worksheets("Contracts").Range("A" & ContractCurrentRow) = .Range("T" & TrackerCurrentRow).Offset(0, 1).Value
Worksheets("Contracts").Range("B" & ContractCurrentRow) = .Range("T" & TrackerCurrentRow).Offset(0, 2).Value
ContractCurrentRow = ContractCurrentRow + 1
End If
TrackerCurrentRow = TrackerCurrentRow + 1
Loop
End With
End Sub
我已经设法解决了它，我确实更改为整数并删除了一些不必要的增量。起初我想我有点困惑。谢谢你的帮助！
Sub ForEach_Loop()
Dim TrackerLastRow As Long
Dim TrackerCurrentRow As Long
Dim ContractLastRow As Long
Dim ContractCurrentRow As Long
TrackerLastRow = 2486
TrackerCurrentRow = 3
ContractCurrentRow = 1
Do While TrackerCurrentRow <= TrackerLastRow
If Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Value = "Yes" Then
Worksheets("Contracts").Range("A" & ContractCurrentRow) = Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Offset(, 1).Value
Worksheets("Contracts").Range("B" & ContractCurrentRow) = Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Offset(, 2).Value
TrackerCurrentRow = TrackerCurrentRow + 1
ContractCurrentRow = ContractCurrentRow + 1
Else
TrackerCurrentRow = TrackerCurrentRow + 1
End If
Loop
End Sub
