vba Excel VBA计数工作表功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44541556/
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
Excel VBA countifs worksheetfunction
提问by Tom36
I am using WorksheetFunction.CountIfs to count records on Sheet2 depending on certain criteria.
我正在使用 WorksheetFunction.CountIfs 根据某些标准对 Sheet2 上的记录进行计数。
The code I'm using is:
我正在使用的代码是：
Dim wksdata As Worksheet
Dim xyz as String
Dim Time as String
Set wksdata = Sheets("Data")
Time = Date + TimeValue("08:00:00") 'Set Time value as todays date 8am
Worksheets("Calculations").Range("I15").Value = Time 'set calculations worksheet cell I15 as the time value
Worksheets("Data)").Range("U:U").NumberFormat = "dd/mm/yyyy hh:mm:ss" 'converts time column to time format
xyz = WorksheetFunction.CountIfs(wksdata.Range("I:I"), "xyz", wksdata.Range("K:K"), "C", wksdata.Range("U:U"), "<" & Worksheets("Calculations").Range("I15").Value)
If I change this to work directly in a worksheet (not VBA) then it works with the same logic.
如果我将其更改为直接在工作表（而不是 VBA）中工作，那么它的工作逻辑相同。
Can anyone point out why it isn't working in VBA and and advice on how to fix it?
任何人都可以指出为什么它在 VBA 中不起作用以及如何解决它的建议？
Thanks
谢谢
回答by gr8tech
Try and use a different variable for setting the date and time. "Time" is built in VBA and will return the current time and so Range("I15").Value will be set to the current time and not 08:00 as required
尝试使用不同的变量来设置日期和时间。“时间”内置在 VBA 中，将返回当前时间等 Range("I15").Value 将设置为当前时间而不是 08:00 根据需要
Dim wksdata As Worksheet
Dim xyz as String
Dim startTime as String
Set wksdata = Sheets("Data")
startTime = Date + TimeValue("08:00:00") 'Set Time value as todays date 8am
Worksheets("Calculations").Range("I15").Value = startTime 'set calculations
worksheet cell I15 as the time value
Worksheets("Data)").Range("U:U").NumberFormat = "dd/mm/yyyy hh:mm:ss"
'converts time column to time format
xyz = WorksheetFunction.CountIfs(wksdata.Range("I:I"), "xyz",
wksdata.Range("K:K"), "C", wksdata.Range("U:U"), "<" &
Worksheets("Calculations").Range("I15").Value)
回答by rohrl77
The date part needs to be converted into a Long. Once this is done, the formula works correctly. Here is a sample of how this could be done using a similar formula:
日期部分需要转换为 Long。完成此操作后，该公式将正常工作。以下是如何使用类似公式完成此操作的示例：
Sub test_method()
Range("B1") = Now()
Range("B1").NumberFormat = "dd/mm/yyyy hh:mm:ss"
'Trying to calculate using a VBA date in the worksheet formula doesn't work
'This returns 0
Debug.Print WorksheetFunction.CountIfs(Range("B1:B10"), ">" & (Now() - 0.75))
'Now we change it to a long and it yields the correct answer
'returns 1
Debug.Print WorksheetFunction.CountIfs(Range("B1:B10"), ">" & CLng((Now() - 0.75)))
End Sub
