vba excel宏中的今天日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19576402/
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
Today`s date in an excel macro
提问by G M
I am trying to add a today`s date to column A in sheet2 each time my macro paste my selection of sheet1 in column B of the new row of the sheet2.
每次我的宏将我选择的 sheet1 粘贴到 sheet2 新行的 B 列中时，我都试图将今天的日期添加到 sheet2 的 A 列中。
My script work to add the new row and paste my selection in sheet2 but I can`t figure out how I could get the date in column A for that new row of sheet2. Here is the script in my macro;
我的脚本工作是添加新行并将我的选择粘贴到 sheet2 中，但我无法弄清楚如何在 sheet2 的新行的 A 列中获取日期。这是我的宏中的脚本；
Sub move()
Dim i As Integer
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Sheet1").Range("A1,A2,A3,A4,A5").Copy
Sheets("Sheet2").Select
i = 3
While Range("B" & i).Value <> ""
i = i + 1
Wend
Range("B" & i).Select
Selection.PasteSpecial (xlValues), Transpose:=True
Worksheets("Sheet1").Range("A1:A5").Clear
End Sub
Any help is appreciated. thanks
任何帮助表示赞赏。谢谢
回答by ARich
Try the Datefunction. It will give you today's date in a MM/DD/YYYY format. If you're looking for today's date in the MM-DD-YYYY format try Date$. Now()also includes the current time (which you might not need). It all depends on what you need. :)
试试这个Date功能。它将以 MM/DD/YYYY 格式为您提供今天的日期。如果您正在寻找 MM-DD-YYYY 格式的今天日期，请尝试Date$. Now()还包括当前时间（您可能不需要）。这一切都取决于你需要什么。:)
回答by David Zemens
Here's an example that puts the Now()value in column A.
这是一个将Now()值放入 A 列的示例。
Sub move()
Dim i As Integer
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim nextRow As Long
Dim copyRange As Range
Dim destRange As Range
Application.ScreenUpdating = False
Set sh1 = ActiveWorkbook.Worksheets("Sheet1")
Set sh2 = ActiveWorkbook.Worksheets("Sheet2")
Set copyRange = sh1.Range("A1:A5")
i = Application.WorksheetFunction.CountA(sh2.Range("B:B")) + 4
Set destRange = sh2.Range("B" & i)
destRange.Resize(1, copyRange.Rows.Count).Value = Application.Transpose(copyRange.Value)
destRange.Offset(0, -1).Value = Format(Now(), "MMM-DD-YYYY")
copyRange.Clear
Application.ScreenUpdating = True
End Sub
There are better ways of getting the last row in column B than using a Whileloop, plenty of examples around here. Some are better than others but depend on what you're doing and what your worksheet structure looks like. I used one here which assumes that column B is ALL empty except the rows/records you're moving. If that's not the case, or if B1:B3have some values in them, you'd need to modify or use another method. Or you could just use your loop, but I'd search for alternatives :)
有比使用While循环更好的方法来获取 B 列的最后一行，这里有很多例子。有些比其他更好，但取决于您在做什么以及您的工作表结构是什么样的。我在这里使用了一个，它假设 B 列除了您要移动的行/记录外都是空的。如果不是这种情况，或者其中B1:B3有一些值，则需要修改或使用其他方法。或者你可以只使用你的循环，但我会寻找替代品:)
