如何用宏实现时间单元格的数据拆分

liwen

Sub Macro1()
'
' Macro1 Macro
' 宏由 李家祥 录制，时间: 2007-8-18
'

'
    Columns("D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Selection.NumberFormatLocal = "yyyy-m-d;@"
    Range("H3").Select
End Sub

录制宏:先选定日期时间栏,在菜单:数据/分列
选分隔符号:空格

liwen

楼上说的不错,
Sub Macro2()

    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=INT(RC[-4])"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]-RC[-1]"
    Range("H2:I2").Select
    Selection.AutoFill Destination:=Range("H2:I11"), Type:=xlFillDefault
    Columns("H:I").Select
    Selection.Copy
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D").Select
    Application.CutCopyMode = False
    Selection.NumberFormatLocal = "yyyy-m-d;@"
    Columns("E:E").Select
    Selection.NumberFormatLocal = "h:mm:ss;@"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "日期"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "时间"
    Range("E1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:I").Select
    Selection.ClearContents
End Sub

这样代码中指定了复制的行数,如复制的行数与实际的行数不一致会出现错误,如真要象这样做也可,不过,你首先要肯定你数据的格式,然后用代码判断你数据倒底用了多少行,复制那么多行的数据也行.