Sous total par suffixe en VBA

Version imprimable

10/11/2011, 11h48
katypati

Sous total par suffixe en VBA

Bonjour à tous,

J'ai un petit soucis de code VBA. Je dois faire des sous totaux mais je n'ai pas les mêmes noms dans mes colonnes par contre j'ai les mêmes suffixes.
Pour vous donner une idée: La colonne de référence pour les sous taux est du genre suivant:
Name
iTraxx Europe Series 13 Version 1 3Y
iTraxx Europe HiVol Series 12 Version 1 5Y
iTraxx Europe Series 12 Version 1 5Y
iTraxx Europe HiVol Series 13 Version 1 5Y
iTraxx Europe Series 13 Version 1 5Y
iTraxx Europe HiVol Series 14 Version 1 5Y
iTraxx Europe Series 14 Version 1 5Y
iTraxx Europe Series 13 Version 1 10Y
iTraxx Europe Series 10 Version 1 5Y
iTraxx Europe Series 10 Version 1 10Y
iTraxx Europe Series 11 Version 1 10Y
iTraxx Europe Series 9 Version 1 5Y
iTraxx Europe Series 9 Version 1 7Y
iTraxx Europe Series 11 Version 1 5Y
iTraxx Europe HiVol Series 10 Version 1 5Y
iTraxx Europe Hivol Series 11 Version 1 5Y
iTraxx Europe Crossover Series 12 Version 2 5Y
iTraxx Europe Crossover Series 13 Version 2 5Y
iTraxx Europe Crossover Series 14 Version 1 5Y
iTraxx Europe Crossover Series 12 Version 2 10Y
iTraxx Europe Crossover Series 13 Version 2 10Y
iTraxx Europe Crossover Series 10 Version 5 5Y
iTraxx Europe Crossover Series 11 Version 2 10Y
iTraxx Europe Crossover Series 11 Version 2 5Y

Je dois faire les sous totaux des prix pour tous les :
iTraxx Europe
iTraxx Europe Hivol
iTraxx Europe Crossover

Merci
10/11/2011, 12h24
Nako_lito

pourquoi ne pas découper en colonne ?
10/11/2011, 15h03
ZebreLoup

Bonjour,

Tu dois absolument faire du VBA ? Ce ne sera pas très compliqué mais tu as plus simple en restant dans Excel :

Tu ajoutes une colonne avec la formule (pour un Excel en français) :

Code:

=GAUCHE(A1;CHERCHE("Series";A1)-2)

Si le nom de ta série est dans la première colonne

Ensuite tu fais des SOMME.SI sur cette colonne et les valeurs.

N'hésite pas si tu veux plus de précisions ou vraiment faire du VBA.
10/11/2011, 16h07
iliesss

bonjour
dans la colonne A designation
iTraxx Europe Series 13 Version 1 3Y
iTraxx Europe HiVol Series 12 Version 1 5Y
iTraxx Europe Series 12 Version 1 5Y
iTraxx Europe HiVol Series 13 Version 1 5Y
iTraxx Europe Series 13 Version 1 5Y
iTraxx Europe HiVol Series 14 Version 1 5Y
iTraxx Europe Series 14 Version 1 5Y
iTraxx Europe Series 13 Version 1 10Y
iTraxx Europe Series 10 Version 1 5Y
iTraxx Europe Series 10 Version 1 10Y
iTraxx Europe Series 11 Version 1 10Y
iTraxx Europe Series 9 Version 1 5Y
iTraxx Europe Series 9 Version 1 7Y
iTraxx Europe Series 11 Version 1 5Y
iTraxx Europe HiVol Series 10 Version 1 5Y
iTraxx Europe Hivol Series 11 Version 1 5Y
iTraxx Europe Crossover Series 12 Version 2 5Y
iTraxx Europe Crossover Series 13 Version 2 5Y
iTraxx Europe Crossover Series 14 Version 1 5Y
iTraxx Europe Crossover Series 12 Version 2 10Y
iTraxx Europe Crossover Series 13 Version 2 10Y
iTraxx Europe Crossover Series 10 Version 5 5Y
iTraxx Europe Crossover Series 11 Version 2 10Y
iTraxx Europe Crossover Series 11 Version 2 5Y

dans la colonne B prix
26 900,00
24 938,00
32 875,00
12 706,00
48 938,00
27 941,00
16 994,00
27 991,00
27 052,00
40 951,00
57 311,00
39 054,00
27 306,00
57 236,00
8 690,00
59 347,00
13 070,00
15 970,00
41 644,00
60 747,00
10 031,00
4 403,00
59 089,00
59 433,00

dans la colonne D
iTraxx Europe
iTraxx Europe Hivol
iTraxx Europe Crossover

colonne E les fonctions suivantes
Code:

1 2 3 =SOMMEPROD((GAUCHE(A2:A25;13)="iTraxx Europe")*(B2:B25)) =SOMMEPROD((GAUCHE(A2:A25;19)="iTraxx Europe Hivol")*(B2:B25)) =SOMMEPROD((GAUCHE(A2:A25;23)="iTraxx Europe Crossover")*(B2:B25))
essayé cet exemple et donne moi ton avis
en plus j'ai un fichier excel comme démonstration
10/11/2011, 16h20
mercatog

@ iliesss
La première compteras aussi la deuxième et troisième. (le critère est vérifié)
10/11/2011, 16h54
katypati

Bonjour à tous ,

merci pour vos réponses.

Je dois faire obligatoirement du VBA. Vu que le nombre de ligne est aléatoires il me sera difficil de le figer. Je dois tout automatisé.

merci.
10/11/2011, 17h57
iliesss

bonjour

salut
si tu veux nous envoyé un fichier demo pour plus de détail et pour que les réponses sont très precis
bon weekend
13/11/2011, 21h12
katypati

1 pièce(s) jointe(s)

Bonsoir,

Vous pouvez trouver en pièce jointe un exemple.

J'ai un fichier dont le nombre de ligne est variable. Ce fichier contient plusieurs colonnes et n'est pas ordonné. Ce que je souhaiterai c'est faire la somme par "name" (mais les "names" sont un peu particulier, je n'ai pas les mêmes "names" mais j'ai la même racine).

Une fois les sous totaux faits j'aimerai les reporter dans le même fichier mais à 3 lignes plus bas .

Mon idée est d'abord de faire un tri. Mais je bloque sur le sous total.

Merci

Si tu as toujours iTraxx comme début

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub SousTotal()
Dim LastLig As Long
Dim c As Range
 
Application.ScreenUpdating = False
With Worksheets("Feuil1")
    .AutoFilterMode = False
    .UsedRange.RemoveSubtotal
    LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
    'On extrait l'info qui nous concerne via la formule en colonne A qu'on vient d'insérer temporairement
    .Columns(1).Insert
    With .Range("A2:A" & LastLig)
        .Formula = "=SUBSTITUTE(LEFT(B2,FIND(""Series"",B2)-2),""iTraxx "","""")"
        .Value = .Value
    End With
    'On tri et on réalise notre sous total
    With .Range("A1:C" & LastLig)
        .Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
        .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End With
    'on reporte l'intitulé des lignes sous totaux (à l'aide d'un filtre automatique et report des intitulé)
    LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
    With .Range("B1:B" & LastLig)
        .AutoFilter Field:=1, Criteria1:=""
        For Each c In .SpecialCells(xlCellTypeVisible)
            c.EntireRow.Font.Bold = True
            If c.Value = "" Then c.Value = c.Offset(0, -1).Value
        Next c
    End With
    .AutoFilterMode = False
    'on supprime la colonne 1
    .Columns(1).Delete
End With
End Sub

13/11/2011, 23h05
iliesss

1 pièce(s) jointe(s)

essayer cet exemple peut être vous aideras
A+

PS : un grand merci pour Mr mercatog c'est impressionnant ton code c'est la vérité.

-
24/11/2011, 01h10
katypati
Merci à tous!

La macro proposé par Mercatog marche seulement j'ai un soucis avec le total du sous total. il se trouve à presque 500 lignes plus bas.

merci pour votre aide.

Re bonsoir!

Une fois les sous totaux obtenus je crée un onglet de synthèses qui contient entre autre les sous totaux.
pour le faire j'utilise la formule index associé à équiv:
Code:

1 2 INDEX(feuil1!$A$5:$P$2088;EQUIV("Total Europe";feuil1!A5:A2088;0);17)
mais à chaque fois que j'éxécute la macro permettant de générer le sous total mes formules ne marche plus.

Est ce que quelqu'un a une idée.

Merci