Need a little help in vba (besoin d'aide sur un code VBA sans pivot table ni sumprod)

gosselien

Board Regular
Joined
Jan 15, 2015
Messages
65
Bonjour,

j'essaye en vain de faire un code VBA pour totaliser des centaines de lignes pour différentes personnes et par jour; les explications sont dans le fichier.
J'y arrive presque, mais par pour les totaux et la répartition par personne en bas du tableau résumé qui commence en colonne N2

Merci de votre aide et de mettre des commentaires dans les lignes que vous ajoutez pour que je comprenne votre code :biggrin:

Je veux faire ça sans sommeprod ni tableau croisé...car j'aimerais comprendre et maitriser un peu les tableaux.

Le fichier est ici: https://www.dropbox.com/s/k0n4wza9fvshwww/consolidation with vba.xlsm?dl=0

Merci !!!!

Patrick
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
The Op would like to create a VBA code to sum hundreds of lines of different people per daily; explanations are in the file.
Code used by OP is:
Code:
Sub Totaux()  'inspiré par un code de J. BOISGONTIER !!!
' no PIVOT TABLE no SUMPROD !
' Patrick
Application.ScreenUpdating = False
Dim Nb As Long
Dim Lun As Long, Mar, Mer, Jeu, Ven, Sam, Dima
Set f1 = Sheets("feuil1")
a = Range("table")  ' zone de A2 à Ixxxx !!!!


Range("N2:W30").ClearContents ' FOR TESTING


Set Mondico1 = CreateObject("Scripting.Dictionary")  ' ne garder qu'une occurence de chaque concaténation colonen A-B-C-D-E
Set mondico2 = CreateObject("Scripting.Dictionary")  ' AJOUTER totaux colonne F-G-H
Set Mondico3 = CreateObject("Scripting.Dictionary")
Set Mondico4 = CreateObject("Scripting.Dictionary")
Set Mondico5 = CreateObject("Scripting.Dictionary")
Set Mondico6 = CreateObject("Scripting.Dictionary")
Set Mondico7 = CreateObject("Scripting.Dictionary")
Set Mondico8 = CreateObject("Scripting.Dictionary")
Set Mondico9 = CreateObject("Scripting.Dictionary")
Set Mondico10 = CreateObject("Scripting.Dictionary")


For i = 1 To UBound(a)
  temp = a(i, 1) & "\" & a(i, 2)  'concaténation avec un "\"
  temp2 = a(i, 1)  ' & "\" & a(i, 2)
  Mondico1(temp) = Mondico1(temp) + a(i, UBound(a, 2))  '
'Debug.Print a(i, UBound(a, 2)), UBound(a, 1)
  mondico2(temp) = mondico2(temp) + a(i, 3)
  Mondico3(temp) = Mondico3(temp) + a(i, 4)
  Mondico4(temp) = Mondico4(temp) + a(i, 5)
  Mondico5(temp) = Mondico5(temp) + a(i, 6)
  Mondico6(temp) = Mondico6(temp) + a(i, 7)
  Mondico7(temp) = Mondico7(temp) + a(i, 8)
  Mondico8(temp) = Mondico8(temp) + a(i, 9)
  Mondico9(temp2) = Mondico9(temp2) + a(i, 3)
  Mondico10(temp2) = Mondico10(temp2) + a(i, 3) + a(i, 4) + a(i, 5) + a(i, 7) + a(i, 8) + a(i, 9)


  Lun = Lun + a(i, 3): Mar = Mar + a(i, 4): Mer = Mer + a(i, 5)
  Jeu = Jeu + a(i, 6): Ven = Ven + a(i, 7): Sam = Sam + a(i, 8): Dima = Dima + a(i, 9)
Next
[N2].Resize(Mondico1.Count) = Application.Transpose(Mondico1.keys)
[n2:n1000].TextToColumns Destination:=Range("n2"), DataType:=xlDelimited, _
                         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                         Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="\"


[p2].Resize(mondico2.Count) = Application.Transpose(mondico2.items)  '
[q2].Resize(Mondico3.Count) = Application.Transpose(Mondico3.items)  '
[r2].Resize(Mondico4.Count) = Application.Transpose(Mondico4.items)  '
[s2].Resize(Mondico5.Count) = Application.Transpose(Mondico5.items)  '
[t2].Resize(Mondico6.Count) = Application.Transpose(Mondico6.items)  '
[u2].Resize(Mondico7.Count) = Application.Transpose(Mondico7.items)  '
[v2].Resize(Mondico8.Count) = Application.Transpose(Mondico8.items)  '




' ????????????????


b = Mondico10.items
For i = 1 To UBound(b)
  s = Split(b(i), "_")
Next


' ????????????????????????????????


Range("P" & [P65000].End(xlUp).Row + 1).Value = Lun
Range("Q" & [Q65000].End(xlUp).Row + 1).Value = Mar
Range("R" & [R65000].End(xlUp).Row + 1).Value = Mer
Range("S" & [S65000].End(xlUp).Row + 1).Value = Jeu
Range("T" & [T65000].End(xlUp).Row + 1).Value = Ven
Range("U" & [U65000].End(xlUp).Row + 1).Value = Sam
Range("V" & [V65000].End(xlUp).Row + 1).Value = Dima


[O15].Resize(Mondico9.Count) = Application.Transpose(Mondico9.keys)  '
[P15].Value = "'? total here ! for personne 1 for Lundi-Mardi etc ..."
[P16].Value = "'???? total here for peronne 2"
' etc etc


' ????????????????????????????????
' ????????????????????????????????


'[P15].Value = s(0)
'[Q15].Value = s(1)
End Sub


Sub ToTessai()
a = Range("table")  ' zone de A2 à Ixxxx !!!!
For i = 1 To UBound(a)
  totp = totp + a(i, 3)
  totq = totq + a(i, 4)
Next
Cells(14, 16) = totp
Cells(14, 17) = totq
End Sub


Sub SupDoublonsColAColBV3()
Application.ScreenUpdating = False
Set f1 = Sheets("BD")
a = f1.Range("A1").CurrentRegion.Value
Set mondico = CreateObject("Scripting.Dictionary")
Set mondico2 = CreateObject("Scripting.Dictionary")
Set Mondico3 = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a)
  temp = a(i, 1) & " / " & a(i, 2)
  mondico(temp) = mondico(temp) + a(i, UBound(a, 2))
  mondico2(temp) = a(i, 1)
  Mondico3(temp) = a(i, 2)
Next
f1.[G1].Resize(mondico.Count) = Application.Transpose(mondico2.items)
f1.[H1].Resize(mondico.Count) = Application.Transpose(Mondico3.items)
f1.[I1].Resize(mondico.Count) = Application.Transpose(mondico.items)
End Sub
 
Upvote 0
Et en attendant une reponse en VBA:

Excel 2013
ABCDEFGHIJ
1PersonneActivitlundimardimercredijeudivendredisamedidimancheTotal
2Jean-PaulSport232154421
3Jean-PaulCourses131312415
4Jean-PaulTravail434111418
5Jean-PaulTV131311414
6PatrickVlo1010101010101070
7EdouardCourses131312415
8EdouardCourses131312415
9EdouardCourses131312415
10EdouardTravail333111416
11EdouardTV131341417
12
13
14
15Jean-Paul68
16Patrick70
17Edouard78
Feuil1
Cell Formulas
RangeFormula
B15=SUMPRODUCT($C$2:$I$11*($A$2:$A$11=$A15))
B16=SUMPRODUCT($C$2:$I$11*($A$2:$A$11=$A16))
B17=SUMPRODUCT($C$2:$I$11*($A$2:$A$11=$A17))



Note: pour un pivot Table vos données ne sont pas bien organisées, il est toujours meilleur d'avoir moins de Columns et plus de Rows. (désolé pour le manque de pratique avec Moliere...)
 
Upvote 0
Et pour une solution avec Pivot.
Vous auriez put generer ceci:
Note: extrait seulement.


Excel 2013
MNOPQRSTUVWXYZ
1PersonneActivitJourCompte
2Jean-PaulCoursesLundi1Pivot table_1
3EdouardCoursesLundi1Pivot countSum of Compte
4EdouardCoursesLundi1Edouard78
5EdouardCoursesLundi1Jean-Paul68
6Jean-PaulCoursesMardi3Patrick70
7EdouardCoursesMardi3Grand Total216
8EdouardCoursesMardi3
9EdouardCoursesMardi3
10Jean-PaulCoursesMercredi1Pivot table_2
11EdouardCoursesMercredi1Pivot activityActivite
12EdouardCoursesMercredi1Pivot countCoursesSportTravailTVVloGrand Total
13EdouardCoursesMercredi1Edouard45161778
14Jean-PaulCoursesJeudi3Jean-Paul1521181468
15EdouardCoursesJeudi3Patrick7070
16EdouardCoursesJeudi3Grand Total6021343170216
17EdouardCoursesJeudi3
18Jean-PaulCoursesVendredi1
19EdouardCoursesVendredi1Pivot table_3
20EdouardCoursesVendredi1Pivot activityJour
21EdouardCoursesVendredi1Pivot countDimancheLundiMardiMercrediJeudiVendrediSamediGrand Total
22Jean-PaulCoursesSamedi2Edouard207157138878
23EdouardCoursesSamedi2Jean-Paul16812888868
24EdouardCoursesSamedi2Patrick1010101010101070
25EdouardCoursesSamedi2Grand Total46253725312626216
26Jean-PaulCoursesDimanche4
27EdouardCoursesDimanche4
28EdouardCoursesDimanche4
29EdouardCoursesDimanche4
30Jean-PaulSportLundi2
31Jean-PaulSportMardi3
Feuil1
 
Upvote 0
Merci et pas de soucis pour la langue de molière mais..

je fais ce tableau en XL2003-2007 et je dois le faire en VBA, donc je ne fais pas de sommeprod ou Pivot Table :(

ça me permet également de mieux connaire le VBA et les tableaux (array) et leur manipulation, j'ai encore du chemin à faire :)

Merci de ton aide :)
 
Upvote 0
Afin d'avoir plus de feedback, que veux-tu que le VBA donne comme resultat?
cela?
Jean Paul 68
patrick 70
Edouard 78

Ou autre chose? et ou?
 
Upvote 0
Bonjour,

je voudrais avoir un tableau en imaginant que les noms en colonne "personne" soient plus nombreux et les jours (des dates) aussi comme indiqué ici;
le tableau de gauche ne peut pas etre modifié ou trié donc je le refais à côté ou dans une autre feuille sans doublons et en séparant les personnes et les jours qui peuvent aller du 01.01.xx au 31.12.xx
Ca me permettra en VBA de comprendre aussi les tableaux car, je rame :) je ne connais pas bien leur manipulation mai scomme c'est très rapide et peu gourmand en mémoire, je voudrais ne pas utiliser sommeprod.
Le tableau sera très rapide et sans pivot ni formule sumprod..



Excel 2007
NOPQRSTUVWXYZ
13Personnelun-03-2015mar-03-2015mer-03-2015jeu-03-2015ven-03-2015sam-03-2015dim-03-2015lun-03-2015mar-03-2015mer-03-2015jeu-03-2015
14Jean-Paul8128888160000
15Patrick101010101010100000
16Edouard71571388200000
17Total253725312626460000

<tbody>
</tbody>
Feuil1
 
Last edited:
Upvote 0
Je déclare d'abord que je ne comprends pas pourquoi vous ne vouvez pas utiliser un tableau croisé dynamique; une méthode formidable de l'agrégation des données inclus dans le prix d'Excel.
Les données publiées ne contiennent pas un champ de date, alors comment voulez-vous obtenir une ventilation par date?
En bref, est il valide la table et la description fournie dans le fichier de l'échantillon ou la description faite dans le dernier message? Dans ce second cas, il aurait besoin d'un fichier de données aligné.
Bye
 
Upvote 0
Je déclare d'abord que je ne comprends pas pourquoi vous ne vouvez pas utiliser un tableau croisé dynamique; une méthode formidable de l'agrégation des données inclus dans le prix d'Excel.
Les données publiées ne contiennent pas un champ de date, alors comment voulez-vous obtenir une ventilation par date?
En bref, est il valide la table et la description fournie dans le fichier de l'échantillon ou la description faite dans le dernier message? Dans ce second cas, il aurait besoin d'un fichier de données aligné.
Bye

Je peux en effet le faire par TCD que je connais depuis 2003 mais la compréhension des tableaux est nouvelle et leur vitesse est formidable quand on les maitrise un peu, ce qui n'est pas mon cas; une réponse en VBA ici m'aurait permis d'étudier la manière de faire en vba et donc les dictionnaires :)
Je pourrais ensuite l'appliquer à d'autres feuilles :)
Merci de ton commentaire :)

Patrick
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top