VBA在Excel中的数据范围之间每天写 [英] VBA to write each day between a data range in Excel
问题描述
我需要在Excel中制作一个表格,要求输入开始日期和结束日期.然后,我需要编写一个VBA脚本,该脚本每天将写出该范围内A列的第一个空白单元格中的内容.
I need to make a form in Excel that asks for a start and end date. Then, I need to write a VBA script that writes out each day within that range in the first blank cell in column A.
例如,如果给定的话:
Start Date: 1/5/2017
End Date: 1/9/2017
结果将是:
1/5/2017
1/6/2017
1/7/2017
1/8/2017
1/9/2017
然后,如果使用新的日期范围再次运行该日期,则日期将追加到列表的底部.这只是一个简短的示例,实际上,日期范围会更大,并且包含几个月.
Then if it is run again with a new date range, the dates would append to the bottom of the list. This is just a short example, in practice the date ranges would be much larger and consist of several months.
我不太确定从哪里开始,所以将不胜感激!
I'm not really sure where to begin with this, so any help would be greatly appreciated!
推荐答案
正如 @Ron Rosenfeld 所述,VBA中的date只是可以通过简单的数字操作增加或减少的数字.该代码应完全满足您的要求:
As @Ron Rosenfeld mentioned, a date in VBA is only a number that can be increased or decreased with simple numeric operations. This code should do exactly what you want:
Dim startDate As Date
Dim endDate As Date
startDate = DateSerial(2017, 1, 1)
endDate = DateSerial(2017, 1, 23)
Dim sheet As Worksheet
Set sheet = Worksheets("Table1")
Dim i As Integer
i = 1
While startDate <= endDate
sheet.Cells(i, 1) = startDate
startDate = startDate + 1
i = i + 1
Wend
这篇关于VBA在Excel中的数据范围之间每天写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!