VBA - Ein Makro zum Erstellen von Pivot-Tabellen mit mehreren Blättern

In diesem Handbuch wird das Objekt "Dictionary" in einem zweidimensionalen Array (Variable) verwendet.
Die Arbeitsmappe

Eine Arbeitsmappe, in der der Umsatz, der Verkäufer und die verkauften Produkte pro Monat zusammengefasst sind.
Das Arbeitsbuch enthält 12 Blätter, eines für jeden Monat.
In jedem dieser Blätter drei Spalten:
- Spalte A: die Namen des Verkäufers,
- Spalte B: die Namen der verkauften Produkte;
- Spalte C: der Betrag.
Der VBA-Code

Kopieren Sie den gesamten folgenden Code, um den VBA in Ihre Arbeitsmappe zu integrieren.
Drücken Sie ALT + F11
Klicken Sie auf Einfügen / Modul
Fügen Sie den Code ein.
Schließen Sie den Visual Basic-Editor, um zu Ihrer Arbeitsmappe zurückzukehren. Drücken Sie dann ALT + F8, wählen Sie " RécapAvecSommeDesColonnesC " und klicken Sie auf "Ausführen".
Ändern Sie zu Ihrer Bequemlichkeit:
- Der Name des Deckblattes
- Die "Quell" -Spalten: A, B und C

Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub
Option Explicit Sub RécapAvecSommeDesColonnesC() Dim Feuille As Worksheet, i As Long Dim TablVendeurs(), DicoVendeurs As Object Dim TablVentes(), DicoVentes As Object Dim Sommes() Set DicoVendeurs = CreateObject("Scripting.Dictionary") Set DicoVentes = CreateObject("Scripting.Dictionary") '*******REMPLISSAGE DES OBJETS DITIONARY ET VARIABLES******* 'remplissage des étiquettes de lignes et de colonnes sans doublons For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille TablVendeurs = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) For i = LBound(TablVendeurs, 1) To UBound(TablVendeurs, 1) If Not DicoVendeurs.exists(TablVendeurs(i, 1)) Then DicoVendeurs.Add TablVendeurs(i, 1), TablVendeurs(i, 1) Next i TablVentes = .Range("B2", .Range("B" & Rows.Count).End(xlUp)) For i = LBound(TablVentes, 1) To UBound(TablVentes, 1) If Not DicoVentes.exists(TablVentes(i, 1)) Then DicoVentes.Add TablVentes(i, 1), TablVentes(i, 1) Next i End With End If Next Feuille 'remplissage de la variable tableau 2D grâce aux clés de Dictionary ReDim Sommes(1 To DicoVendeurs.Count, 1 To DicoVentes.Count) For Each Feuille In ThisWorkbook.Worksheets If Feuille.Name "Récap" Then With Feuille For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) = Sommes(Application.Match(.Cells(i, 1), DicoVendeurs.keys, 0), Application.Match(.Cells(i, 2), DicoVentes.keys, 0)) + .Range("C" & i).Value Next i End With End If Next Feuille '*******RESTITUTION DES DONNEES******* With Sheets("Récap") .Range("A2").Resize(DicoVendeurs.Count, 1) = Application.Transpose(DicoVendeurs.keys) .Range("B1").Resize(1, DicoVentes.Count) = DicoVentes.keys .Range("B2").Resize(UBound(Sommes, 1), UBound(Sommes, 2)) = Sommes() End With End Sub