Excel VBA - 日期格式自动更改

Excel VBA - the date format changes automatically

提问by HL8

I'm trying to enter the Date value by adding a month to the date in Sheets("Sheet1").Cells(17, 3).Value which is 01/10/2011 but format as Oct-11. Then return in Sheets("Sheet1").Cells(17, 4).Value = LDate, to show Nov-11

我试图通过向 Sheets("Sheet1").Cells(17, 3).Value 中的日期添加一个月来输入日期值，该值是 01/10/2011，但格式为 Oct-11。然后在 Sheets("Sheet1").Cells(17, 4).Value = LDate 中返回，以显示 Nov-11

sDate = Sheets("Sheet1").Cells(17, 3).Value   --> this shows 01/10/2011 when I hove over sDate
LDate = DateAdd("m", 1, sDate)                --> this shows 01/11/2011 when I hove over LDate

I then want to enter that value 01/11/2011 in to the following cell

然后我想在以下单元格中输入该值 01/11/2011

Sheets("Sheet1").Cells(17, 4).Value = LDate
Selection.NumberFormat = "mmm-yy"

But in the cell it shows 11/01/2011 (Jan-11), why is it doing this and how can I fix this issue?

但在单元格中显示 11/01/2011（1 月 11 日），为什么要这样做，我该如何解决这个问题？

回答by Tony Dallimore

Minor issue

小问题

Selection.NumberFormat = "mmm-yy"

You have not selected anything so this format is placed wherever you left the cursor.

您尚未选择任何内容，因此此格式将放置在您离开光标的任何位置。

Major issue

主要问题

You have hit the Excel bug that it will interpret dates as being in American (middle endian) format if it can when transferring data to a worksheet. "1/11/2011" would be "11 Jan 11" to an American so it is to Excel. "20/11/2011" is not a valid American date so to Excel it is "20 Nov 11".

您遇到了 Excel 错误，即在将数据传输到工作表时，它会将日期解释为美国（中端）格式。“1/11/2011”对美国人来说是“11 Jan 11”，对 Excel 来说也是如此。“20/11/2011”不是有效的美国日期，因此对于 Excel，它是“20 11 月 11 日”。

I can duplicate your problem by declaring sDate and LDate as strings. DateAddworks correctly with strings so LDate is correct but when placed in a cell it is misinterpreted.

我可以通过将 sDate 和 LDate 声明为字符串来复制您的问题。 DateAdd与字符串一起正常工作，因此 LDate 是正确的，但是当放置在单元格中时，它会被误解。

I can fix your problem by declaring sDate and LDate as dates:

我可以通过将 sDate 和 LDate 声明为日期来解决您的问题：

Dim sdate As Date
Dim Ldate As Date

回答by Siva Charan

Selection.NumberFormat = "mmm-yy"

Above line is changing the number format. Actually When am running through your steps, I have come across the same issue.

上面的行正在更改数字格式。实际上，当我执行您的步骤时，我遇到了同样的问题。

But Number format is doing all this auto change.

但是数字格式正在执行所有这些自动更改。

Example:

例子：

1. While am running the macro, assume that selected cell is (17,3).

   • Before running macro: cell value is 01/11/2012
   • After running macro: cell value will be changed/formatted as "Jan-12". (Since number format is applied as "mmm-yy", so it consider as 01/11/2012is consider as Jan-12)

2. Second time, if you select the cell (17,4)

   • Before running macro: cell value is 2/11/2012
   • After running macro: cell value will be changed / formatted as "Feb-12"

1. 在运行宏时，假设选定的单元格是(17,3).

   • 运行宏之前：单元格值为 01/11/2012
   • 运行宏后：单元格值将被更改/格式化为"Jan-12". （由于数字格式应用为"mmm-yy"，所以它认为01/11/2012是 考虑为Jan-12）

2. 第二次，如果您选择单元格 (17,4)

   • 运行宏之前：单元格值为 2/11/2012
   • 运行宏后：单元格值将被更改/格式化为 "Feb-12"

回答by Glen

Instead of trying

而不是尝试

Cells(row2,col2) = Cells(row1,col1)

Try this

尝试这个

Cells(row1,col1).Copy
Cells(row2,col2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats