Bonjour le forum,
je souhaiterais synthétiser la formule ci-dessous qui correspond a 1 colonne sachant qu'il y a 16 colonnes fois 12 lignes.
le chiffre en rouge correspond à un indice se trouvant dans BD (Base de donnée) et il y en a 16 autres.
sub MàJ()
Worksheets("Recap").Range("c3") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b3)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c4") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b4)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c5") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b5)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c6") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b6)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c7") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b7)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c8") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b8)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c9") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b9)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c10") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b10)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c11") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b11)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c12") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b12)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c13") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b13)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c14") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b14)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
end sub
dans l'attente merci
je souhaiterais synthétiser la formule ci-dessous qui correspond a 1 colonne sachant qu'il y a 16 colonnes fois 12 lignes.
le chiffre en rouge correspond à un indice se trouvant dans BD (Base de donnée) et il y en a 16 autres.
sub MàJ()
Worksheets("Recap").Range("c3") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b3)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c4") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b4)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c5") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b5)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c6") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b6)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c7") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b7)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c8") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b8)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c9") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b9)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c10") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b10)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c11") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b11)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c12") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b12)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c13") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b13)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
Worksheets("Recap").Range("c14") = Application.Evaluate("sumproduct((MONTH(BD!b2:b65536)=MONTH(Recap!b14)*(YEAR(BD!b2:b65536)=YEAR(Recap!b1)))*(BD!g2:g65536=" & "69" & "))")
end sub
dans l'attente merci