Microsoft 365 Calcul d'appareils de soufflage suivant critéres

Option Explicit
Function OptiSouf(ByVal Surf As Double, ByVal Effect As Double, AirNeuf As Double, ByVal RngTb As Range) As Variant()
   Dim TRef(), MeilVol As Double, L As Integer, LMeil As Integer, Nb As Integer, Vol As Double, MeilNb As Integer, MeilL As Integer
   If Effect = 0 Then OptiSouf = Array(0, ""): Exit Function
   TRef = RngTb.Value
   MeilVol = (2 ^ 53 - 1) * 2 ^ 971
   For L = 1 To UBound(TRef, 1)
      Nb = Int(AirNeuf / TRef(L, 2) + 0.9999999)
      Vol = Nb * TRef(L, 2)
      If (Surf < 50 Imp Nb = 1) And MeilVol > Vol Then MeilVol = Vol: MeilNb = Nb: MeilL = L
      Next L
   If MeilL > 0 Then
      OptiSouf = Array(MeilNb, TRef(MeilL, 1))
   Else: OptiSouf = Array("Impossible", ""): End If
   End Function