Microsoft 365 Convertir une formule en VBA

Sub Calcule()
T0 = Timer
Dim tablo, R()
Derlig = Application.WorksheetFunction.CountA(Range("A:A"))
ReDim R(Derlig)
tablo = Range("A1:B" & Derlig)
For L = 2 To Derlig
    If tablo(L, 2) Like "*VENTE*" Then
        If tablo(L, 1) > 0 Then
            R(L - 1) = 1
        End If
    Else
        R(L - 1) = 0
    End If
Next L
Cells(1, 3).Resize(UBound(R)) = Application.Transpose(R)
MsgBox Round(Timer - T0, 3) & " s"
End Sub

=SI(NB.SI.ENS(Data[Libellé];"*VENTE*";Data[Référence];Data[[#Cette ligne];[Référence]])>0;1;0)

Private Sub CommandButton1_Click()
Dim t#, critere$, tablo, d As Object, i&
t = Timer
critere = CStr([D1]) 'critère à adapter
With [A1].CurrentRegion
    .AutoFilter: .AutoFilter 'si le tableau est filtré
    If FilterMode Then ShowAllData
    tablo = .Resize(, 2) 'matrice, plus rapide
End With
'---comptage---
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tablo)
    If CStr(tablo(i, 2)) Like critere Then d(CStr(tablo(i, 1))) = 1
Next i
'---tableau des résultats---
tablo(1, 1) = critere
For i = 2 To UBound(tablo)
    tablo(i, 1) = -d.exists(CStr(tablo(i, 1)))
Next i
'---restitution---
[D1].Resize(UBound(tablo)) = tablo
MsgBox "Durée du calcul " & Format(Timer - t, "0.00 \s")
End Sub