函数在2个值之间检查失败时返回空白

Question

我试图从单元格传递一个值，并检查它是否在2列的值之间，并相应地拉出第三个单元格值，但它总是返回空白，传入的值格式为10:20:30，其中10是度，20是分钟，30是秒和apple所以包含数据的表格就像

apple red new 0:00:00   5:00:00
apple blue new 5:00:00   8:00:00
apple green new 8:00:00   11:00:00
apple yellow new 11:00:00   15:00:00
bat red new 0:00:00   5:00:00
bat blue new 5:00:00   8:00:00
bat green new 8:00:00   11:00:00
bat yellow new 11:00:00   15:00:00

所以output should be "green"是空的

Function FindMD(chkname As String, chkdegree As String) As String
Dim Result As String
Dim start_degree, end_degree
Dim count1, count2, count3, chkseconds1, chkseconds2, chkseconds3
For Each element1 In Split(chkdegree, ":")
count1 = count1 + 1

If count1 = 1 Then
chkseconds1 = chkseconds1 + (element1 * 240)
End If
If count1 = 2 Then
chkseconds1 = chkseconds1 + (element1 * 60)
End If
If count1 = 3 Then
chkseconds1 = chkseconds1 + element1
End If
Next element1

For Each C In Worksheets("degrees").Range("A2:A250")
If C.Value = chkname Then

start_degree = C.Offset(0, 3).Value
end_degree = C.Offset(0, 4).Value

For Each element2 In Split(start_degree, ":")
count2 = count2 + 1
If count2 = 1 Then
chkseconds2 = chkseconds2 + (element2 * 240)
End If
If count2 = 2 Then
chkseconds2 = chkseconds2 + (element2 * 60)
End If
If count2 = 3 Then
chkseconds2 = chkseconds2 + element2
End If
Next element2

For Each element3 In Split(end_degree, ":")
count3 = count3 + 1
If count3 = 1 Then
chkseconds3 = chkseconds3 + (element3 * 240)
End If
If count3 = 2 Then
chkseconds3 = chkseconds3 + (element3 * 60)
End If
If count3 = 3 Then
chkseconds3 = chkseconds3 + element3
End If
Next element3



If chkseconds2 <= chkseconds1 Then
If chkseconds1 < chkseconds3 Then        
Result = C.Offset(0, 1).Value
End If
End If

End If
Next C
FindMD = Result
End Function

Answer 1

确实需要提取度>>秒转换为函数。

未经测试:

Function FindMD(chkname As String, chkdegree As String) As String
Dim Result As String, secIn As Long, c As Range

secIn = DegreeToSec(chkdegree)
For Each c In Worksheets("degrees").Range("A2:A250")
If c.Value = chkname Then
If secIn > DegreeToSec(c.Offset(0, 3).Value) Then
If secIn < DegreeToSec(c.Offset(0, 4).Value) Then
Result = c.Offset(0, 1).Value
Exit For
End If
End If
End If
Next c
FindMD = Result
End Function
Function DegreeToSec(d As String) As Long
Dim arr
arr = Split(d, ":")
DegreeToSec = 3600 * arr(0) + 60 * arr(1) + arr(2)
End Function