VBA - मल्टी-शीट पिवट टेबल बनाने के लिए एक मैक्रो

इस गाइड में हम "डिक्शनरी" ऑब्जेक्ट का उपयोग द्वि-आयामी सरणी (चर) में करेंगे।
कार्यपुस्तिका

एक कार्यपुस्तिका बिक्री, प्रति माह, विक्रेता और उत्पादों को बेचती है।
कार्यपुस्तिका में 12 पत्रक होते हैं, प्रत्येक महीने के लिए एक।
इनमें से प्रत्येक शीट में, तीन कॉलम:
- कॉलम ए: सेल्समैन के नाम,
- कॉलम बी: बेचे गए उत्पादों के नाम,
- कॉलम सी: राशि।
VBA कोड

VBA को अपनी कार्यपुस्तिका में एकीकृत करने के लिए, नीचे दिए गए संपूर्ण कोड की प्रतिलिपि बनाएँ।
ALT + F11 दबाएं
इंसर्ट / मॉड्यूल पर क्लिक करें
कोड पेस्ट करें।
अपनी कार्यपुस्तिका पर लौटने के लिए विज़ुअल बेसिक एडिटर को बंद करें, फिर ALT + F8 दबाएं, " RécapAvecSommeDesColonnesC " चुनें और फिर "रन" पर क्लिक करें।
अपनी सुविधा में परिवर्तन करें:
- रेक शीट का नाम
- "स्रोत" कॉलम: ए, बी और सी

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