VBA Excel分离单元格中的日期范围

Question

这让我头疼.. 我正在尝试做以下事情：

这是我的数据，我有元素名称、开始日期和结束日期。 我想把这些数据按天而不是范围（这样我就可以将其上传到我按天分类的数据库中）。

我不知道是否可以不使用VBA来完成此操作，但我想最快的方式是使用VBA。

目前的数据：

╔═══════╦════════════╦════════════╗
║ name  ║ start date ║  end date  ║
╠═══════╬════════════╬════════════╣
║ foo1  ║ 25-11-2013 ║ 28-11-2013 ║
║ foo2  ║ 25-11-2013 ║ 28-11-2013 ║
║ foo3  ║ 25-11-2013 ║ 28-11-2013 ║
║ foo4  ║ 25-11-2013 ║ 28-11-2013 ║
║ foo5  ║ 25-11-2013 ║ 28-11-2013 ║
║ foo6  ║ 28-11-2013 ║ 28-11-2013 ║
║ foo7  ║ 28-11-2013 ║ 28-11-2013 ║
║ foo8  ║ 28-11-2013 ║ 28-11-2013 ║
║ foo9  ║ 28-11-2013 ║ 28-11-2013 ║
║ foo10 ║ 28-11-2013 ║ 28-11-2013 ║
║ foo11 ║ 29-11-2013 ║ 30-11-2013 ║
║ foo12 ║ 29-11-2013 ║ 30-11-2013 ║
║ foo13 ║ 29-11-2013 ║ 30-11-2013 ║
║ foo14 ║ 29-11-2013 ║ 30-11-2013 ║
║ foo15 ║ 29-11-2013 ║ 30-11-2013 ║
╚═══════╩════════════╩════════════╝

我希望你能够把名字按照日期分开，实现如下效果：

╔═══════╦════════════╗
║ name  ║    date    ║
╠═══════╬════════════╣
║ foo1  ║ 25-11-2013 ║
║ foo2  ║ 25-11-2013 ║
║ foo3  ║ 25-11-2013 ║
║ foo4  ║ 25-11-2013 ║
║ foo5  ║ 25-11-2013 ║
║ foo1  ║ 26-11-2013 ║
║ foo2  ║ 26-11-2013 ║
║ foo3  ║ 26-11-2013 ║
║ foo4  ║ 26-11-2013 ║
║ foo5  ║ 26-11-2013 ║
║ foo1  ║ 27-11-2013 ║
║ foo2  ║ 27-11-2013 ║
║ foo3  ║ 27-11-2013 ║
║ foo4  ║ 27-11-2013 ║
║ foo5  ║ 27-11-2013 ║
║ foo6  ║ 28-11-2013 ║
║ foo7  ║ 28-11-2013 ║
║ foo8  ║ 28-11-2013 ║
║ foo9  ║ 28-11-2013 ║
║ foo10 ║ 28-11-2013 ║
║ foo11 ║ 29-11-2013 ║
║ foo12 ║ 29-11-2013 ║
║ foo13 ║ 29-11-2013 ║
║ foo14 ║ 29-11-2013 ║
║ foo15 ║ 29-11-2013 ║
║ foo11 ║ 30-11-2013 ║
║ foo12 ║ 30-11-2013 ║
║ foo13 ║ 30-11-2013 ║
║ foo14 ║ 30-11-2013 ║
║ foo15 ║ 30-11-2013 ║
╚═══════╩════════════╝

提前感谢您。

- gepex

---

+1 针对格式良好的问题！您是如何创建该帖子的表格格式的？它看起来很漂亮和易读 :) - Blackhawk

这些类似文本的表格应该被标准化，以满足需要示例的Excel问题...非常好。 - sancho.s ReinstateMonicaCellio

你可以使用复制/粘贴来完成这个操作... 但是一旦你将它们分开，你怎么知道日期是开始日期还是结束日期呢？这不重要吗？这似乎应该是两个字段，而不是一个。 - David Zemens

开始日期和结束日期是否在同一列？您只是想提取开始日期吗？ - James

@Blackhawk 链接 - gepex

Answer 1

结合@SorenHoltenHansen的答案，这应该可以帮助您达到目标。这个类将接受一个开始和结束日期范围，并计算出完整的日期范围，然后您可以在代码中使用。

创建一个新类，称其为“clsDateRange”，并添加以下代码：

Option Compare Database
Option Explicit

Private m_colDates As Collection

Public Sub InitStartEnd(ByVal dtStart As Date, ByVal dtEnd As Date)
    Set m_colDates = New Collection
    Dim tempDate As Date
    For tempDate = dtStart To dtEnd Step 1
        m_colDates.Add DateValue(tempDate)
    Next
End Sub

Public Property Get Dates() As Collection
    Set Dates = m_colDates
End Property

你可以全面实现集合接口，但这应该足以满足你的需求。如果你将拥有非常大的日期范围并且想要更加智能地处理它，你可以仅存储开始和结束日期，并在需要时生成中间日期，但我希望能够使用For...Each而无需定义[_NewEnum]和Collection的所有子属性。

以下是一个名为“mdlMain”的模块中的一些测试，以便你了解如何使用它：

Public Sub Main()
    Dim oDateRange As New clsDateRange
    Dim varDate As Variant

    oDateRange.InitStartEnd "25-11-2013", "27-11-2013"
    For Each varDate In oDateRange.Dates()
        MsgBox varDate
    Next

    oDateRange.InitStartEnd "28-11-2013", "28-11-2013"
    For Each varDate In oDateRange.Dates()
        MsgBox varDate
    Next

    oDateRange.InitStartEnd "29-11-2013", "30-11-2013"
    For Each varDate In oDateRange.Dates()
        MsgBox varDate
    Next

End Sub

---

顺便提一下，日期实际上只是64位浮点数，Doubles。它们代表从公元100年1月1日到9999年12月31日的范围。每天为1，整个范围为[-657434，2958465]。一天中的时间以小数部分表示。午夜为*.0，中午为*.5，3:30为~ *.645833333333333。当前（在我的时区），是2013年12月6日下午1:27。根据VBA在立即窗口内?CDbl(now())，那是41614.5608680556。

所以这就是我可以通过for循环遍历日期范围并每次添加一天来增加天数的原因。

- Blackhawk

Answer 2

最快、最简单的方法可能是使用VBA。以下代码循环遍历列A和C中的值，将这些值写在现有数据的列A和B下方，并删除列C中的数据。

Sub SeperateDateRange()
    Dim Ws As Worksheet
    Dim nCol As Integer

    'Define sheet
    Set Ws = ActiveSheet

    nCol = 1 '<~~ Defines the number of columns before the date columns

    Application.ScreenUpdating = False

    'Loops throuh cells
    For i = 1 To ActiveSheet.Cells(Rows.Count, nCol + 2).End(xlUp).Row - 1 Step 1
        For j = 0 To Ws.Cells(i + 1, nCol + 2).Value - Ws.Cells(i + 1, nCol + 1).Value Step 1

            With Ws.Cells(Ws.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
                For k = 0 To nCol - 1 Step 1
                    .Offset(0, k).Value = Ws.Cells(i + 1, k + 1).Value
                Next k
                .Offset(0, nCol).Value = DateSerial(Year(Ws.Cells(i + 1, nCol + 1).Value), Month(Ws.Cells(i + 1, nCol + 1).Value), Day(Ws.Cells(i + 1, nCol + 1).Value) + j)
            End With
        Next j
    Next i

    'Deletes last column with dates
    Ws.Cells(1, nCol + 2).EntireColumn.Delete

    Application.ScreenUpdating = True
End Sub

更新：由于在评论中有一个后续问题，代码现已更改，以便变量nCol定义了日期列之前具有名称的列数。如果宏应该在原始问题中提供的数据上运行，则nCol = 1。如果日期变量之前有三列带名称，则nCol = 3。

- Netloh

---

你缺少了日期之间的天数。如果我有一行记录，名称为“foo”，从“2013年11月25日”到“2013年11月27日”，那么结果将是三行，名称为“foo”，日期分别为“2013年11月25日”，“2013年11月26日”和“2013年11月27日”。 - gepex

我明白了 - 我已经更新了代码。希望这样就能完成任务了。 - Netloh

很抱歉再次询问，但如果在日期之前有更多的列（name1，name2，name3），我应该在代码中添加它们的位置在哪里？ - gepex

这取决于您希望它们在最终表格中如何列出。您是否希望Name1，Name2和Name3都在同一列中？ - Netloh

不，不同的列...与您之前所做的相同，但在日期之前有更多的信息列，输出是相同的信息列和日期列。 - gepex

抱歉回复晚了。我已经更新了上面的代码。我加入了一个变量 nCol。你可以使用它来设置日期变量前的列数。例如，如果你有三个名称列，你应该使用 nCol = 3 运行代码。 - Netloh