格式列与日期特定格式 - Excel VBA [英] Format column with date to specific format - Excel VBA

问题描述

我正在写一个excel vba宏。

我有一个超过10,000行的大型工作表。
其中一列具有以下格式的日期： 10/28/13 06:57 ，单元格具有用于格式化的一般编号。

我想将其格式化为只有四位数字： mmdd （上面的示例为1028）

这是我到目前为止在子程序中：

 '这个子程序将列格式化为输出所需的
 Sub formatColumns（）
'设置LastRow变量以保存最后一行号
 lastRow = Cells.Find（*，[A1] ，xlByRows，xlPrevious）.Row 
 
 Dim cell As Range 
 
对于范围内的每个单元格（B3：B& lastRow）
 cell.NumberFormat =mmdd; @
下一个
 End Sub 
  

解决方案

您不能使用 mmdd 数字格式进行格式化。至少对我来说不行。 （Excel 2010 / Win 7 <欧洲区域设置）

我建议添加一个额外的列并复制数据以保持原始格式，然后将该列隐藏到最后。同时，您将使用 Month（）和 Day（）函数创建所需的格式。

  Sub formatColumns（）
 Dim lastRow As Long 
 
'将LastRow变量设置为持有最后一行号
 lastRow = Cells.Find（*，[A1]，，，xlByRows，xlPrevious）.Row 
 
 Dim cell As Range 
 For Each cell In Range（B3：B& lastRow）
'cell.NumberFormat =mmdd; @
 cell.Insert Shift：= xlToRight，CopyOrigin：= xlFormatFromLeftOrAbove 
 cell.Offset 0，-1）.NumberFormat =@
 cell.Offset（0，-1）= _ 
 IIf（Len（Month（cell））= 1，0& Month ），月（单元格））& _ 
 IIf（Len（Day（cell））= 1，0& Day（cell），Day（cell））
 Next 
 
列（C： C）。EntireColumn.Hidden = True 
 End Sub 
  

---

如果您不想添加额外的列，但您不介意丢失原始格式，那么您可以使用此代码替换单元格的内容

  Sub formatColumns（）
 Dim lastRow As Long 
 
'将LastRow变量设置为保存最后一行数
 lastRow = Cells.Find（*，[A1]，，，xlByRows，xlPrevious）.Row 
 
 Dim cell As Range 
对于每个单元格的范围（B3： B& lastRow）
'cell.NumberFormat =mmdd; @
 cell = IIf（Len（Month（cell））= 1，0& Month（cell）细胞））& _ 
 IIf（Len（Day（cell））= 1，0& Day（cell），Day（cell））
下一个
 
 End Sub 
  

I am writing an excel vba macro.

I have a large worksheet with over 10,000 rows. One of the columns has the date in this format: 10/28/13 06:57 with the Cells having a General Number for formatting.

I would like to format it to have only four digits in this format: mmdd (1028 for the example above)

Here is what I have so far in the subroutine:

' This subroutine formats the columns to what is necessary for output
Sub formatColumns()
    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Dim cell As Range

    For Each cell In Range("B3:B" & lastRow)
        cell.NumberFormat = "mmdd;@"
    Next
End Sub

解决方案

You can't really format it that way using that mmdd number format. At least it doesn't work for me. (Excel 2010/ Win 7 European Locale)

What I'd suggest it's adding an extra column and copying over the data to keep the original format then hiding that column in the end. Meanwhile, you would use Month() and Day() functions to create the desired format.

Sub formatColumns()
    Dim lastRow As Long

    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Dim cell As Range
    For Each cell In Range("B3:B" & lastRow)
        'cell.NumberFormat = "mmdd;@"
        cell.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        cell.Offset(0, -1).NumberFormat = "@"
        cell.Offset(0, -1) = _
            IIf(Len(Month(cell)) = 1, "0" & Month(cell), Month(cell)) & _
            IIf(Len(Day(cell)) = 1, "0" & Day(cell), Day(cell))
    Next

    Columns("C:C").EntireColumn.Hidden = True
End Sub

---

if you don't want to add an extra column but you dont mind loosing the original format then you can just replace the contents of the cells using this code

Sub formatColumns()
    Dim lastRow As Long

    ' Set the LastRow variable to hold the last row number
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    Dim cell As Range
    For Each cell In Range("B3:B" & lastRow)
        'cell.NumberFormat = "mmdd;@"
        cell = IIf(Len(Month(cell)) = 1, "0" & Month(cell), Month(cell)) & _
        IIf(Len(Day(cell)) = 1, "0" & Day(cell), Day(cell))
    Next

End Sub

这篇关于格式列与日期特定格式 - Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！