Recherche et extraction de résultats sur plusieurs feuilles

Type MyValeur
A As Double
B As Double
C As Double
D As Double
E As Double
F As Double
G As Double
H As Double
End Type

Type MyProduct
A As Double
B As Double
C As Double
D As Double
E As Double
F As Double
G As Double
H As Double
End Type

Sub test()
Dim Arr(), Sh As Worksheet, Trouve As Range
Dim DerLig As Long, C As Range, Arr1()
Dim MyVal As MyValeur, MyProD As MyProduct

Arr = Array("Bon 3%", "BON CADEAU", "BON REDUCTION", "CB MANUELLE", _
    "ESPECES", "FACTURETTE MANUELLE", "OD", "BALANCE")
Arr1 = Array("A16:B16", "D16:E16", "G16:H16", "J16:K16", _
    "A21:B21", "D21:E21", "G21:H21", "J21:K21")
For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> "RECAP" Then
    DerLig = Sh.Range("F:F").Find(What:="*", _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
    With Sh.Range("F4:F" & DerLig)
        For Each C In .Cells
            Select Case UCase(C.Value)
                Case Is = Arr(0)
                    MyVal.A = MyVal.A + C.Offset(, -1).Value
                    MyProD.A = MyProD.A + C.Offset(, -3).Value
                Case Is = Arr(1)
                    MyVal.B = MyVal.B + C.Offset(, -1).Value
                    MyProD.B = MyProD.B + C.Offset(, -3).Value
                Case Is = Arr(2)
                    MyVal.C = MyVal.C + C.Offset(, -1).Value
                    MyProD.C = MyProD.C + C.Offset(, -3).Value
                Case Is = Arr(3)
                    MyVal.D = MyVal.D + C.Offset(, -1).Value
                    MyProD.D = MyProD.D + C.Offset(, -3).Value
                Case Is = Arr(4)
                    MyVal.E = MyVal.E + C.Offset(, -1).Value
                    MyProD.E = MyProD.E + C.Offset(, -3).Value
                Case Is = Arr(5)
                    MyVal.F = MyVal.F + C.Offset(, -1).Value
                    MyProD.F = MyProD.F + C.Offset(, -3).Value
                Case Is = Arr(6)
                    MyVal.G = MyVal.G + C.Offset(, -1).Value
                    MyProD.G = MyProD.G + C.Offset(, -3).Value
                Case Is = Arr(7)
                    MyVal.H = MyVal.H + C.Offset(, -1).Value
                    MyProD.H = MyProD.H + C.Offset(, -3).Value
            End Select
        Next
    End With
    End If
Next
Application.EnableEvents = False
With Worksheets("RECAP")
    .Range(Arr1(0))(1, 2) = MyVal.A
    .Range(Arr1(0))(1) = MyProD.A
    .Range(Arr1(1))(1, 2) = MyVal.B
    .Range(Arr1(1))(1) = MyProD.B
    .Range(Arr1(2))(1, 2) = MyVal.C
    .Range(Arr1(2))(1) = MyProD.C
    .Range(Arr1(3))(1, 2) = MyVal.D
    .Range(Arr1(3))(1) = MyProD.D
    .Range(Arr1(4))(1, 2) = MyVal.E
    .Range(Arr1(4))(1) = MyProD.E
    .Range(Arr1(5))(1, 2) = MyVal.F
    .Range(Arr1(5))(1) = MyProD.F
    .Range(Arr1(6))(1, 2) = MyVal.G
    .Range(Arr1(6))(1) = MyProD.G
    .Range(Arr1(7))(1, 2) = MyVal.H
    .Range(Arr1(7))(1) = MyProD.H
End With
Application.EnableEvents = False
End Sub