VBA 如果时间介于两次之间

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9318954/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 15:15:14  来源:igfitidea点击:

VBA If time is between two times

vbadatetimecompare

提问by Jordan

i = 1    
For Each cell In Range("D1:D10000")
  If IsEmpty(cell.Value) Then Exit For

  Select Case Range("N" & i).Value
    Case 0 To 40
      startTime  = "13:00"
    Case 40 To 60
      secondTime = Range("V" & i).Value
    Case 60 To 100
      finalTime = Range("V" & i).Value
    Case Else

   End Select

  Dim startTime As Date
  Dim secondTime As Date
  Dim finalTime As Date
  Dim timeRef As Date
  timeRef = Range("V" & i).Value

  If timeRef >= startTime And timeRef < secondTime Then 

  ElseIf timeRef >= secondTime And timeRef < finalTime Then

  ElseIf timeRef > finalTime Then

  End If
  i = i + 1
Next

Ok, So i'm using the above to try and compare times which are formatted as Dates. They are all fetched from a worksheet which is Custom Formatted as "hh:mm" but I cannot seem to get times to fall within any but the final elseif

好的,所以我使用上面的方法来尝试比较格式为日期的时间。它们都是从自定义格式为“hh:mm”的工作表中获取的,但我似乎没有时间落入除最终elseif

Argh!

啊!

回答by Ken White

You're declaring the values after you've assigned to them (startTime, secondTimeand finalTime). At a minimum, you need to change your code to declare and then assign. (Also, your first if statement is wrong; the first test should simply be If timeRef < secondTime.)

您在分配给它们(startTime,secondTimefinalTime)之后声明这些值。至少,您需要更改代码以声明然后分配。(此外,您的第一个 if 语句是错误的;第一个测试应该只是If timeRef < secondTime。)

' Declare variables before assigning values to them
Dim startTime As Date
Dim secondTime As Date
Dim finalTime As Date
Dim timeRef As Date

i = 1    
For Each cell In Range("D1:D10000")
  If IsEmpty(cell.Value) Then Exit For

  Select Case Range("N" & i).Value
    Case 0 To 40
      startTime  = "13:00"
    Case 40 To 60
      secondTime = Range("V" & i).Value
    Case 60 To 100
      finalTime = Range("V" & i).Value
    Case Else
      ' You should do something here; what if the cell is empty or invalid?
      ' You end up with no value assigned to anything when leaving the Select
   End Select

  timeRef = Range("V" & i).Value

  If timeRef < secondTime Then 

  ElseIf timeRef >= secondTime And timeRef < finalTime Then

  ElseIf timeRef > finalTime Then

  End If
  i = i + 1
Next

Since I don't have any of your data to test against, I have no idea if this will actually work, but it fixes the obvious issues that would preventit from working.

由于我没有您的任何数据可供测试,我不知道这是否真的有效,但它解决了会阻止它工作的明显问题。