Trier par groupe en fonction des valeurs

Sub test()
Dim tabValeurs() As Variant, nbLignesTab As Long, nbLignes As Long, i As Long
Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long
    
    With ThisWorkbook.Sheets("Resultat")
        nbLignesTab = WorksheetFunction.Max( _
            Evaluate("SumProduct((" & .Range("G6:G300").Address & ">=0)*(" & .Range("G6:G300").Address & "<=25)*1)"), _
            Evaluate("SumProduct((" & .Range("G6:G300").Address & ">25)*(" & .Range("G6:G300").Address & "<=50)*1)"), _
            Evaluate("SumProduct((" & .Range("G6:G300").Address & ">50)*(" & .Range("G6:G300").Address & "<=75)*1)"), _
            Evaluate("SumProduct((" & .Range("G6:G300").Address & ">75)*(" & .Range("G6:G300").Address & "<=100)*1)"))
        
        ReDim tabValeurs(1 To nbLignesTab, 1 To 4)
        
        nbLignes = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 6 To nbLignes
            If .Range("G" & i).Value >= 0 And .Range("G" & i).Value <= 25 Then
                i1 = i1 + 1
                tabValeurs(i1, 1) = .Range("B" & i).Value
            ElseIf .Range("G" & i).Value > 25 And .Range("G" & i).Value <= 50 Then
                i2 = i2 + 1
                tabValeurs(i2, 2) = .Range("B" & i).Value
            ElseIf .Range("G" & i).Value > 50 And .Range("G" & i).Value <= 75 Then
                i3 = i3 + 1
                tabValeurs(i3, 3) = .Range("B" & i).Value
            ElseIf .Range("G" & i).Value > 75 And .Range("G" & i).Value <= 100 Then
                i4 = i4 + 1
                tabValeurs(i4, 4) = .Range("B" & i).Value
            End If
        Next i
    End With

    ThisWorkbook.Sheets("Groupe").Range("B3").Resize(UBound(tabValeurs, 1), UBound(tabValeurs, 2)).Value = tabValeurs

End Sub

=SI(LIGNE()-2>SOMMEPROD((Resultat!$G$6:$G$205>SUBSTITUE(STXT(B$2;TROUVE("de";B$2)+3;2);" ";"")*1)*(Resultat!$G$6:$G$205<=STXT(B$2;TROUVE("à";B$2)+2;NBCAR(B$2)-TROUVE("à";B$2)-2)*1)*1);"";INDIRECT("Resultat!B"&PETITE.VALEUR(((Resultat!$G$6:$G$205>SUBSTITUE(STXT(B$2;TROUVE("de";B$2)+3;2);" ";"")*1)*(Resultat!$G$6:$G$205<=STXT(B$2;TROUVE("à";B$2)+2;NBCAR(B$2)-TROUVE("à";B$2)-2)*1)*LIGNE(Resultat!$G$6:$G$205));LIGNES(Resultat!$G$6:$G$205)-SOMMEPROD((Resultat!$G$6:$G$205>SUBSTITUE(STXT(B$2;TROUVE("de";B$2)+3;2);" ";"")*1)*(Resultat!$G$6:$G$205<=STXT(B$2;TROUVE("à";B$2)+2;NBCAR(B$2)-TROUVE("à";B$2)-2)*1)*1)+(LIGNE()-2))))