VBA Excel单元格中的日期范围 [英] VBA Excel separate range of date in cells

问题描述

这是给我一个头痛..我试图做以下：

这是我的数据，我有元素的名字和开始日期和结束日期。
我希望有一天这个数据，而不是在范围内（所以我可以将它上传到一个数据库，我的日期是这样的）。

我不知道我是否可以在不使用VBA的情况下执行此操作，但我猜想快速将是VBA。

当前数据：

 ╔═══════════════════════════════$ $ $ $ $ $ ║名║开始日期║结束日期║
╠═════════════════════════════ $ b║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║
║f oo10║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║
╚══════════════════════════════ $ b  

我想按日分隔de名称，以获取这个：

 ╔══════════════════╗
║名称║日期║
 ╠════════════════════$║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║ $ b║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║
╚══════════════════╝
  

提前谢谢。

解决方案

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

  Sub SeperateDateRange（）
 Dim Ws As Worksheet 
 Dim nCol As Integer 
 
'定义表
设置Ws = ActiveSheet 
 
 nCol = 1'< ~~定义日期列之前的列数
 
 Application.ScreenUpdating = False 
 
'循环细胞
对于i = 1到ActiveSheet.Cells（Rows.Count，nCol + 2）.End（xlUp）.Row  -  1步骤1 
对于j = 0 To Ws.Cells（i + 1，nCol + 2）.Value  -  Ws.Cells（i + 1，nCol + 1）.Value步骤1 
 
用Ws.Cells（Ws.Cells（Rows。计数，1）.End（xlUp）.Row + 1，1）
对于k = 0到nCol  -  1步骤1 
 .Offset（0，k）.Value = Ws.Cells（i + 1，k + 1）.Value 
下一个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）
结束
下一步j 
下一步i 
 
'删除最后一列
 Ws.Cells（1，nCol + 2）.EntireColumn.Delete 
 
 Application.ScreenUpdating = True 
 End Sub 
  pre> 
 
 
 更新：由于注释中的后续问题，代码现在已更改，因此变量 nCol 定义具有日期列之前的名称的列数。如果宏应该运行在原始问题中提供的数据，那么 nCol = 1 。如果有三列名称在日期之前有效，那么 nCol = 3 。 
 
This is giving me a headache.. Im trying to do the following:

This is the data I have, I have element's names and a start date and an end date.
I want to have this data by day and not in range anymore (So I can upload it to a database I have which is by day).

I dont know if I can do this without using VBA but I guess the quickes would be VBA.

Current data:
╔═══════╦════════════╦════════════╗
║ 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 ║
╚═══════╩════════════╩════════════╝
And I want to separate de names by day, to obtain this:
╔═══════╦════════════╗
║ 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 ║
╚═══════╩════════════╝
Thank you in advance.
解决方案
The fastest and easiest way might be to use VBA. The following code loops through the values from column A and C, writes these underneath the existing data in column A and B and removes data in column 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
UPDATE: Because of a follow-up question in the comments, the code is now changed so that the variable nCol defines the number of columns with names before the date columns. If the macro should run on the data presented in the original question, then nCol = 1. If there are three columns with names before the date viables, then nCol = 3. 

                        
这篇关于VBA Excel单元格中的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！