Première occurrence dans plage variable

Sub Test()
Dim i As Long, Derli As Long, x As String
Derli = Range("B" & Rows.Count).End(xlUp).Row
i = 1: x = ""
Do Until i = Derli + 1
  If IsNumeric(Cells(i, 1)) And Cells(i, 1) <> "" And Cells(i, 2) <> x Then
    Cells(i, 4) = Cells(i, 1)
    x = Cells(i, 2)
  Else
    Cells(i, 4) = 0
  End If
  i = i + 1
Loop
End Sub

Sub Test2()
Dim C As Range, i As Long, x As String, Tablo
With Range("A1:A" & Cells(Rows.Count, 2).End(xlUp).Row)
  i = 1: x = ""
  ReDim Tablo(1 To .Cells.Count)
  For Each C In .Cells
    If IsNumeric(C) And C <> "" And C.Offset(o, 1) <> x Then
      Tablo(i) = C: x = C.Offset(o, 1)
    Else
      Tablo(i) = 0
    End If
    i = i + 1
  Next
  Range("D1").Resize(UBound(Tablo)) = Application.Transpose(Tablo)
End With
End Sub

=MAX(SI($B$1:B1=$B$2:B2;"";LIGNE($B$2:B2)))

=MIN(SI(B2:$B$16000<>B3:$B$16001;LIGNE(B2:$B$16000);""))

=EQUIV(VRAI;ESTNUM(CNUM(SUPPRESPACE(INDIRECT("A" & C2 & ":A" & D2))));0)

=SI(LIGNES(INDIRECT("A" & C2 & ":A" & LIGNE()))=E2;SI(ESTNUM(CNUM(SUPPRESPACE(A2)));CNUM(SUPPRESPACE(A2));"");"")