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

[gosselien]

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

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

 

[gosselien]

little up

remise à jour
Merci

 

[cyrilbrd]

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:

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

 

[cyrilbrd]

Et en attendant une reponse en VBA:

Excel 2013
	A	B	C	D	E	F	G	H	I	J
1	Personne	Activit	lundi	mardi	mercredi	jeudi	vendredi	samedi	dimanche	Total
2	Jean-Paul	Sport	2	3	2	1	5	4	4	21
3	Jean-Paul	Courses	1	3	1	3	1	2	4	15
4	Jean-Paul	Travail	4	3	4	1	1	1	4	18
5	Jean-Paul	TV	1	3	1	3	1	1	4	14
6	Patrick	Vlo	10	10	10	10	10	10	10	70
7	Edouard	Courses	1	3	1	3	1	2	4	15
8	Edouard	Courses	1	3	1	3	1	2	4	15
9	Edouard	Courses	1	3	1	3	1	2	4	15
10	Edouard	Travail	3	3	3	1	1	1	4	16
11	Edouard	TV	1	3	1	3	4	1	4	17
12
13
14
15	Jean-Paul	68
16	Patrick	70
17	Edouard	78
Feuil1

Cell Formulas
Range	Formula
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...)

 

[cyrilbrd]

Et pour une solution avec Pivot.
Vous auriez put generer ceci:
Note: extrait seulement.

Excel 2013
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Personne	Activit	Jour	Compte
2	Jean-Paul	Courses	Lundi	1		Pivot table_1
3	Edouard	Courses	Lundi	1		Pivot count	Sum of Compte
4	Edouard	Courses	Lundi	1		Edouard	78
5	Edouard	Courses	Lundi	1		Jean-Paul	68
6	Jean-Paul	Courses	Mardi	3		Patrick	70
7	Edouard	Courses	Mardi	3		Grand Total	216
8	Edouard	Courses	Mardi	3
9	Edouard	Courses	Mardi	3
10	Jean-Paul	Courses	Mercredi	1		Pivot table_2
11	Edouard	Courses	Mercredi	1		Pivot activity	Activite
12	Edouard	Courses	Mercredi	1		Pivot count	Courses	Sport	Travail	TV	Vlo	Grand Total
13	Edouard	Courses	Mercredi	1		Edouard	45		16	17		78
14	Jean-Paul	Courses	Jeudi	3		Jean-Paul	15	21	18	14		68
15	Edouard	Courses	Jeudi	3		Patrick					70	70
16	Edouard	Courses	Jeudi	3		Grand Total	60	21	34	31	70	216
17	Edouard	Courses	Jeudi	3
18	Jean-Paul	Courses	Vendredi	1
19	Edouard	Courses	Vendredi	1		Pivot table_3
20	Edouard	Courses	Vendredi	1		Pivot activity	Jour
21	Edouard	Courses	Vendredi	1		Pivot count	Dimanche	Lundi	Mardi	Mercredi	Jeudi	Vendredi	Samedi	Grand Total
22	Jean-Paul	Courses	Samedi	2		Edouard	20	7	15	7	13	8	8	78
23	Edouard	Courses	Samedi	2		Jean-Paul	16	8	12	8	8	8	8	68
24	Edouard	Courses	Samedi	2		Patrick	10	10	10	10	10	10	10	70
25	Edouard	Courses	Samedi	2		Grand Total	46	25	37	25	31	26	26	216
26	Jean-Paul	Courses	Dimanche	4
27	Edouard	Courses	Dimanche	4
28	Edouard	Courses	Dimanche	4
29	Edouard	Courses	Dimanche	4
30	Jean-Paul	Sport	Lundi	2
31	Jean-Paul	Sport	Mardi	3
Feuil1

 

[gosselien]

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

 

[cyrilbrd]

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?

 

[gosselien]

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

	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
Personne
Jean-Paul
Patrick
Edouard
Total

<tbody>
[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"]lun-03-2015[/TD]
[TD="align: right"]mar-03-2015[/TD]
[TD="align: right"]mer-03-2015[/TD]
[TD="align: right"]jeu-03-2015[/TD]
[TD="align: right"]ven-03-2015[/TD]
[TD="align: right"]sam-03-2015[/TD]
[TD="align: right"]dim-03-2015[/TD]
[TD="align: right"]lun-03-2015[/TD]
[TD="align: right"]mar-03-2015[/TD]
[TD="align: right"]mer-03-2015[/TD]
[TD="align: right"]jeu-03-2015[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Feuil1​

 

[Anthony47]

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

 

[gosselien]

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