Excel - Horas y Application.MATCH

valorHora = Format(CDate(TextBox5), "HH:MM")

    'valorHora = TextBox5.Value

    valorFila = Application.Match(valorHora, Range("A:A"), 0)

    If Not IsError(valorFila) Then

       Cells(valorFila, 2).Value = "OK"

    Else

         Beep

         MsgBox "ERROR"

    End If

Option Explicit

Sub test()

Dim H$, fil&, i%

Dim hora$

hora = "00:30"  '//Hora buscada

H = "00:00"     '//Hora inicial

fil = 1         '//Fila inicial

For i = 1 To 96

    If H = hora Then Exit For

    H = SumarTiempo(H, 15 * 60000)

    fil = fil + 1

Next

MsgBox fil

End Sub

Private Function SumarTiempo(ByVal sTime As String, ByVal deltaT As Long) As String

Dim H$, m$, s$, ms$, st$, t&, sSigno$

On Error Resume Next

st = sTime

'st = VBA.Replace(st, ".", ":")

H = VBA.Split(st, ":")(0)

m = VBA.Split(st, ":")(1)

's = VBA.Split(st, ":")(2)

'ms = VBA.Split(st, ":")(3)

t = Val(H) * 3600000 + Val(m) * 60000 ' + Val(s) * 1000 + Val(ms) * 10

t = t + deltaT '//Calcula nuevo valor

'//Si el resultado es negativo

If t < 0 Then

   sSigno = "-"

   t = t * (-1)

End If

H = VBA.Format((t \ 3600000), "00")

m = VBA.Format((t \ 60000) Mod 60, "00")

's = VBA.Format((t \ 1000) Mod 60, "00")

'ms = VBA.Format((t Mod 1000) \ 10, "00")

SumarTiempo = sSigno & H & ":" & m '& ":" & s '& "." & ms

End Function

Sheets("Eventos3").Select

valorfila = Application.Match(Range("ZZ1"), Range("A:A"), 1)

If Not IsError(valorfila) Then

   Cells(valorfila, 2).Value = "OK"

Else

   MsgBox "ERROR"

End If

'------------------------------------------------------------------------------------

End Sub

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   Range("ZZ1") = CDate(TextBox5)

End Sub