Reputation: 1501
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
Argh!
Upvotes: 1
Views: 12706
Reputation: 125767
You're declaring the values after you've assigned to them (startTime
, secondTime
and 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
.)
' 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 prevent it from working.
Upvotes: 1