将特定列的日期格式设置为“yyyy-mm-dd”的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12324190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code to set date format for a specific column as "yyyy-mm-dd"
提问by Kristina
I have a macro which I specify the date (in mm/dd/yyyy) in a textbox and I want to set this value for column A in yyyy-mm-dd format. I have the following code:
我有一个宏，我在文本框中指定日期（以 mm/dd/yyyy 为单位），我想以 yyyy-mm-dd 格式为 A 列设置此值。我有以下代码：
Sheets("Sheet1").Range("A2", "A50000").Value = TextBox3.Value
Sheet1.Range("A2", "A50000") = Format(Date, "yyyy-mm-dd")
...and when I run the macro, the date is still in mm/dd/yyyy format.
...当我运行宏时，日期仍然是 mm/dd/yyyy 格式。
How can I change this so that it is in the format I want?? I've been trying many kinds of code researched through google and nothing will set the format the way I want it.
如何更改它以使其成为我想要的格式？我一直在尝试通过谷歌研究的多种代码，但没有任何东西可以按照我想要的方式设置格式。
Any help will be appreciated...
任何帮助将不胜感激...
EDIT: Full code from OP's comment below:
编辑：来自下面OP评论的完整代码：
Workbooks.Add
Range("A1") = "Acctdate"
Range("B1") = "Ledger"
Range("C1") = "CY"
Range("D1") = "BusinessUnit"
Range("E1") = "OperatingUnit"
Range("F1") = "LOB"
Range("G1") = "Account"
Range("H1") = "TreatyCode"
Range("I1") = "Amount"
Range("J1") = "TransactionCurrency"
Range("K1") = "USDEquivalentAmount"
Range("L1") = "KeyCol"
Sheets("Sheet1").Range("A2", "A50000").Value = TextBox3.Value
Sheet1.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd"
回答by LittleBobbyTables - Au Revtheitroad
Use the range's NumberFormatproperty to force the format of the range like this:
使用范围的NumberFormat属性来强制范围的格式是这样的：
Sheet1.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd"
回答by Doug Glancy
You are applying the formatting to the workbook that has the code, not the added workbook. You'll want to get in the habit of fully qualifying sheet and range references. The code below does that and works for me in Excel 2010:
您正在将格式应用于具有代码的工作簿，而不是添加的工作簿。您需要养成完全限定工作表和范围引用的习惯。下面的代码可以做到这一点，并且在 Excel 2010 中对我有用：
Sub test()
Dim wb As Excel.Workbook
Set wb = Workbooks.Add
With wb.Sheets(1)
.Range("A1") = "Acctdate"
.Range("B1") = "Ledger"
.Range("C1") = "CY"
.Range("D1") = "BusinessUnit"
.Range("E1") = "OperatingUnit"
.Range("F1") = "LOB"
.Range("G1") = "Account"
.Range("H1") = "TreatyCode"
.Range("I1") = "Amount"
.Range("J1") = "TransactionCurrency"
.Range("K1") = "USDEquivalentAmount"
.Range("L1") = "KeyCol"
.Range("A2", "A50000").Value = Me.TextBox3.Value
.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd"
End With
End Sub
回答by Brane77
It works, when you use both lines:
当您使用这两行时，它会起作用：
Application.ActiveWorkbook.Worksheets("data").Range("C1", "C20000") = Format(Date, "yyyy-mm-dd")
Application.ActiveWorkbook.Worksheets("data").Range("C1", "C20000").NumberFormat = "yyyy-mm-dd"
