VBA 声明日期/时间数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39642529/
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 Declare date/time data
提问by Argyris
I ve got hourly data and when I try to execute the following code I get an runtime error 91.The format of my data in the CYC sheet is for example #07/07/2009 23:00:00# (at row 194), but when I enter this to dt it automatically converts it to #7/7/2009 11:00:00 PM#. (please note that shtCYC and shtCo have been declared and set).
我有每小时数据，当我尝试执行以下代码时，出现运行时错误 91。例如，CYC 表中数据的格式为 #07/07/2009 23:00:00#（第 194 行） ，但是当我将其输入到 dt 时，它会自动将其转换为 #7/7/2009 11:00:00 PM#。（请注意 shtCYC 和 shtCo 已被声明和设置）。
Dim dt As Date
dt = #7/7/2009 11:00:00 PM#
Do
shtCYC.Activate
'finds the day
Set rng = shtCYC.Range("A3:A1514").Find(dt, , xlValues)
'copies the dates
shtCYC.Range("A" & rng.Row - 191 & ":A" & rng.Row + 24).Copy (this is where the debug highlights)
shtCO.Range("B10").PasteSpecial Paste:=xlPasteValues
Anyone got any ideas..? Many Many thanks!
任何人有任何想法..？非常感谢！
回答by Siddharth Rout
Well that is not the only problem that I see. See the code below.
嗯，这不是我看到的唯一问题。请参阅下面的代码。
- To find dates you have to use DateValue because of various formatting reasons.
- You need to check if a value was found
- You need to check if the
rng.Rowfalls in a specific range
- 由于各种格式原因，要查找日期必须使用 DateValue。
- 您需要检查是否找到了值
- 您需要检查是否
rng.Row落在特定范围内
I have explained it in comments. Let me know if you still have questions.
我已经在评论中解释过了。如果您还有问题，请告诉我。
Sub Sample()
Dim dt As Date
Dim shtCYC As Worksheet
Dim Rng As Range
dt = #7/7/2009 11:00:00 PM#
Set shtCYC = ActiveSheet '<~~ Change this to the relevant sheet
With shtCYC
Set Rng = .Range("A3:A1514").Find(what:=DateValue(dt), LookIn:=xlFormulas)
'~~> Check If match found
If Not Rng Is Nothing Then
'~~> This Check is required because what if the rng.row is <=191 or >=1048552?
'~~> I understand that you have mentioned the range as "A3:A1514"
'~~> But tom if you use .Cells then?
'~~> Rng.Row - 191 / Rng.Row + 24 will give you error in that case
If Rng.Row > 191 Or Rng.Row < (.Rows.Count - 24) Then
.Range("A" & Rng.Row - 191 & ":A" & Rng.Row + 24).Copy
'shtCO.Range("B10").PasteSpecial Paste:=xlPasteValues
End If
Else
MsgBox "Match Not Found"
End If
End With
End Sub
Tested in Excel 2013. My Worksheet looks like this.
在 Excel 2013 中测试。我的工作表如下所示。
回答by Argyris
You might have more success locating the datetime by using the worksheet's MATCH function. I often have issues with the Range.Find methodwhen searching for values containing times.
通过使用工作表的MATCH 函数，您可能会更成功地定位日期时间。在搜索包含时间的值时，我经常遇到 Range.Find 方法的问题。
Dim dt As Date, rw As Variant
Dim shtCYC As Worksheet, shtCO As Worksheet
dt = #7/7/2009 11:00:00 PM#
Debug.Print dt
Set shtCYC = Worksheets("Sheet4") '<~~ set the source worksheet
Set shtCO = Worksheets("Sheet5") '<~~ set the target worksheet
With shtCYC
'finds the row containing the datetime value
rw = Application.Match(CDbl(dt), .Columns(1), 0)
If Not IsError(rw) Then
'dt was found, transfer the block of values
If rw > 194 Then
shtCO.Range("B10").Resize(216, 1) = _
.Cells(rw, 1).Resize(216, 1).Value
Else
Debug.Print rw & " not large enough to encompass all values."
End If
Else
Debug.Print dt & " not found."
End If
End With
Note that this is using direct value transfer rather than Copy & Paste. It is a more efficient method of transferring xlPasteValues as it does not involve the clipboard.
请注意，这是使用直接值传输而不是复制和粘贴。这是一种更有效的 xlPasteValues 传输方法，因为它不涉及剪贴板。
