Excel VBA, Determining which row to fill in based on other data

Question

So I have two tables like this:
(Assume Tidal Time (1st table) is Column A)
(Tidal Height (1st table) is Column B)

Tidal Time  Tidal Height
00:00   
01:00   
02:00   
03:00   
04:00   
05:00   
06:00   
07:00   
08:00   
09:00   
10:00   
11:00   
12:00   
13:00   
14:00   
15:00   
16:00   
17:00   
18:00   
19:00   
20:00   
21:00   
22:00   
23:00

(Assume Tidal Time (2nd Table) is Column C)
(Tidal Height (2nd Table) is Column D)

Tidal Time  Tidal height
04:16   1.6 m
10:24   4.8 m
16:31   1.7 m
22:38   4.7 m

The idea is to fill in the value at the correct place on table 1 based on the value written in column C on table 2. This is how I envisioned it:

If (Sheets("Vessels").Range("A10").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A11") > Sheets("Vessels").Range("C9")) Then
   Sheets("Vessels").Range("B10") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A11").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A12") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B11") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A12").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A13") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B12") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A13").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A14") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B13") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A14").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A15") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B14") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A15").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A16") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B15") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A16").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A17") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B16") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A17").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A18") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B17") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A18").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A19") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B18") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A19").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A20") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B19") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A20").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A21") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B20") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A21").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A22") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B21") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A22").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A23") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B22") = Sheets("Vessels").Range("D9")
   ElseIf (Sheets("Vessels").Range("A23").Value < Sheets("Vessels").Range("C9")) And (Sheets("Vessels").Range("A24") > Sheets("Vessels").Range("B10")) Then
    Sheets("Vessels").Range("B23") = Sheets("Vessels").Range("D9")

End If

The idea behind the code: The time in Column A is lower than the time in Column C and the next time down in Column A is higher than the time in Column C.
Therefore if true:
This must be the corrosponding time so fill the value in D (that is next to the time in C) into Column B in this row.

For some reason this just doesn't work. I have no idea why and I'm slightly confused. Can anyone come up with a better way or point out my mistakes?
Thank you in advance!

Answer 1

The below is function written using multiple excel functions Its same like looping through vba code. Below is the formula which need to pasted in cell B2 and then pull the cell content till the end of column A values. =IF(AND(A1<C$2,C$2<A2),VLOOKUP(C$2,C:D,2,0),IF(AND(A1<C$3,C$3<A2),VLOOKUP(C$3,C:D,2,0),IF(AND(A1<C$4,C$4<A2),VLOOKUP(C$4,C:D,2,0),IF(AND(A1<C$5,C$5<A2),VLOOKUP(C$5,C:D,2,0),""))))

Answer 2

Just use a double-loop:

Sub Demo()
  Dim i As Long, j As Long, t As Date, v As String
  For i = 2 To 5
    t = Cells(i, "C").Value
    v = Cells(i, "D").Value
    For j = 3 To 25
      If t > Cells(j - 1, "A").Value And t < Cells(j, "A").Value Then
        Cells(j, "B").Value = v
      End If
    Next j
  Next i
End Sub