VBA - แมโครเพื่อสร้างตารางเดือยหลายแผ่น

ในคู่มือนี้เราจะใช้วัตถุ "พจนานุกรม" ในอาร์เรย์สองมิติ (ตัวแปร)
สมุดงาน

สมุดงานจัดกลุ่มการขายต่อเดือนพนักงานขายและผลิตภัณฑ์ที่ขาย
เวิร์กบุ๊กมี 12 แผ่นสำหรับแต่ละเดือน
ในแต่ละชีตเหล่านี้สามคอลัมน์:
- คอลัมน์ A: ชื่อพนักงานขาย
- คอลัมน์ B: ชื่อผลิตภัณฑ์ที่ขาย
- คอลัมน์ C: จำนวนเงิน
รหัส VBA

หากต้องการรวม VBA เข้ากับสมุดงานของคุณให้คัดลอกรหัสทั้งหมดด้านล่าง
กด ALT + F11
คลิกที่ แทรก / โมดูล
วางรหัส
ปิดตัวแก้ไข Visual Basic เพื่อกลับไปยังสมุดงานของคุณแล้วกด ALT + F8 เลือก " RécapAvecSommeDesColonnesC " แล้วคลิก "เรียกใช้
เปลี่ยนเพื่อความสะดวกของคุณ:
- ชื่อแผ่นชีทrécap
- คอลัมน์ "แหล่งที่มา": A, B และ 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