我有一个活动表格,其中X表示频率(每隔X个月进行一次),第一次开始日期和结束日期如下:
我该如何复制每一行并将其粘贴到一个新的工作表中,根据X增加每个基于月份的日期,并添加另外一行,如下所示:
Sub test()
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Dim x&, cnt&, cl As Range, SDt$, EDt$, Dif As Date, Key As Variant
With Sheets("Source")
x = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cl In .Range(.[A2], .Cells(x, "A"))
cnt = 1
Dic.Add cnt & ";" & cl.Text & ";" & cl.Offset(, 2).Text & ";" & cl.Offset(, 3).Text, Nothing
Dif = DateAdd("m", cl.Offset(, 1).Value, cl.Offset(, 3).Value)
While Year(Dif) = 2015
cnt = cnt + 1
SDt = Right("0" & Month(Dif), 2) & "-" & Right("0" & Day(cl.Offset(, 2).Value), 2) & "-" & Year(Dif)
EDt = Right("0" & Month(Dif), 2) & "-" & Right("0" & Day(cl.Offset(, 3).Value), 2) & "-" & Year(Dif)
Dic.Add cnt & ";" & cl.Text & ";" & SDt & ";" & EDt, Nothing
Dif = DateAdd("m", cl.Offset(, 1).Value, Dif)
Wend
Next cl
End With
Sheets("Output").Activate: x = 2 ''
With Sheets("Output")
For Each Key In Dic
.Range(.Cells(x, 1), Cells(x, 4)) = Split(Key, ";")
x = x + 1
Next Key
.[C1:D1].Value = Sheets("Source").[C1:D1].Value
.[B1] = Sheets("Source").[A1]
.[A1] = "TASK ITERATION"
End With
End Sub
初始表格