Excel VBA 中的嵌套 For 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42793222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Nested For Loop in Excel VBA
提问by Ahmed Dildar
The following code is working for only when c = 2,however I want it to work for other values as well. Below is the Excel table on which I want to run it.
以下代码仅适用于c = 2，但我希望它也适用于其他值。下面是我想要运行它的 Excel 表。
Date PickupCost StorageCost DeliveryCost
1/1/2017 140 35 0
1/8/2017 80 20 0
1/10/2017 0 0 149
1/30/2017 35 8 0
I want to fill data of each date missing but only the value at column 3 (StorageCost) needs to be same in other missing date values as previous day's StorageCostvalue.
我想填充每个缺失日期的数据，但只有第 3 ( StorageCost)列的值在其他缺失的日期值中需要与前一天的StorageCost值相同。
Dim j, p, w, c As Long
Dim date1, date2 As Date
j = Cells(Rows.Count, 1).End(xlUp).Row
w = 2
For c = w To j
date1 = Range("A" & w).Value
date2 = Range("A" & (w + 1)).Value
p = DateDiff("d", date1, date2)
For w = 2 To p
Range("A" & (c + 1)).EntireRow.Insert
ActiveSheet.Cells(c + 1, 1).Value = (ActiveSheet.Cells(c, 1).Value) + 1
ActiveSheet.Cells(c + 1, 2).Value = 0
ActiveSheet.Cells(c + 1, 3).Value = ActiveSheet.Cells(c, 3).Value
ActiveSheet.Cells(c + 1, 4).Value = 0
c = c + 1
Next w
w = w + 1
ActiveSheet.Range("A1").Select
j = Cells(Rows.Count, 1).End(xlUp).Row
Next c
回答by ArchiCAT
Your main problem is that once you defined your For c = w To jloop then it will only run until it reaches value j had when you defined the for loop.
If you want an endpoint to the loop that adapts to the runtime changing number of rows, you should use a Do Untilloop, like this:
您的主要问题是，一旦您定义了For c = w To j循环，它就会运行直到达到定义 for 循环时的 j 值。如果您希望循环的端点适应运行时更改的行数，则应使用Do Until循环，如下所示：
Dim p As Long
Dim c, w As Integer
Dim date1, date2 As Date
c = 2
Do Until c = Cells(Rows.Count, 1).End(xlUp).Row
date1 = Range("A" & c).Value
date2 = Range("A" & (c + 1)).Value
p = DateDiff("d", date1, date2)
For w = c To c + p - 2
Range("A" & (w + 1)).EntireRow.Insert
ActiveSheet.Cells(w + 1, 1).Value = (ActiveSheet.Cells(c, 1).Value) + 1
ActiveSheet.Cells(w + 1, 2).Value = 0
ActiveSheet.Cells(w + 1, 3).Value = ActiveSheet.Cells(c, 3).Value
ActiveSheet.Cells(w + 1, 4).Value = 0
c = c + 1
Next w
c = c + 1
Loop
