Select Case : Procédure trop longue

Le 20/10/2015 à 10:59

Bonjour et enchanté,

Je suis nouveau sur ce forum donc j'espère avoir mis ce sujet au bon endroit.

Je rencontre un soucis avec une macro répétitive. J'avais tout d'abord commencé avec des IF et ElseIf tout en ayant peu d'espoirs puis je suis passé à la formule SelectCase. Cependant la procédure est encore trop lourde.

Dans mon fichier j'ai:

4 onglets contenant des bases: "Extractions Stocks France" "Extractions Ventes France" "Extraction Pays du Sud" et "Informations from 027_EstractW15" (L'orthographe laisse à désirer pour le dernier )

1 onglets d'analyse ou je viens rentrer mes chiffres pour chaque semaine avec des somme.si dans les colonnes correspondantes.

Ce que je demande à ma macro c'est que, suivant le numéro de la semaine, contenu dans la colonne A2 de l'onglet "Extractions Ventes France", elle vienne effectuer mes calcules dans les colonnes correspondantes et qu'elle les étire jusqu'à la dernière ligne. une fois cela fait, elle vient copier les résultats en valeurs.

Les semaines vont de 27 à 53 (Collection Hiver)

Je n'ai pas réussi à réellement comprendre comment éviter de recopier toute la procédure pour chaque semaine en remplaçant à chaque fois les colonnes ce qui fait que j'arrive maintenant à une quantité de code beaucoup trop importante et c'est pourquoi je me tourne vers vous.

Avant de passer au code: pour fixer l'étirement de mes formule jusqu'à la dernière ligne j'ai dû recréer un Dim derligne(n) As long ... pour chaque semaine sans quoi la macro renvoyait une erreur. Cependant c'est depuis que j'ai ajouté tout ces critères que la macro est devenu trop lourde.

Ci dessous le code des deux première semaine comme je l'ai écrit:

Sheets("Extractions Ventes France").Select

Dim numbersemaine As Integer
numbersemaine = Range("B2")

Select Case numbersemaine
'---------------------------------------------------------------- SEMAINE 27 ------------------------------------------------------------------------

Case 27

Sheets("Ventes Détaillées").Select

    Range("HQ4").Select

    ActiveCell.FormulaR1C1 = _
"=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C20),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C13))"

    Range("HQ4").Select
    Selection.Copy
    Range("HR4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C21),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C14))"
    Range("HR4").Select
    Selection.Copy
    Range("HS4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C22),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C15))"
    Range("HS4").Select
    Selection.Copy
    Range("HT4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",SUMIF('Extraction Stock France'!C[-275]:C[-274],'Ventes Détaillées'!RC4,'Extractions Ventes France'!C38),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C16))"
    Range("HT4").Select
    Selection.Copy
    Range("HU4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",0,SUMIF('Extractions Pays du Sud'!C12:C17,'Ventes Détaillées'!RC[-306],'Extractions Pays du Sud'!C17))"
    Range("HU5").Select

Sheets("Ventes Détaillées").Select
Dim DernLigne2 As Long
DernLigne2 = Range("A" & Rows.Count).End(xlUp).Row
Range("HQ4").Select
Selection.AutoFill Destination:=Range("HQ4:HQ" & DernLigne2)

Range("HR4").Select
Selection.AutoFill Destination:=Range("HR4:HR" & DernLigne2)

Range("HS4").Select
Selection.AutoFill Destination:=Range("HS4:HS" & DernLigne2)

Range("HT4").Select
Selection.AutoFill Destination:=Range("HT4:HT" & DernLigne2)

Range("HU4").Select
Selection.AutoFill Destination:=Range("HU4:HU" & DernLigne2)

Calculate

    Range("HQ4:HU4").Select
    Range("HU4").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

'---------------------------------------------------------------- SEMAINE 28 ------------------------------------------------------------------------

Case 28

Sheets("Ventes Détaillées").Select

    Range("HW4").Select

    ActiveCell.FormulaR1C1 = _
"=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C20),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C13))"

    Range("HW4").Select
    Selection.Copy
    Range("HX4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C21),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C14))"
    Range("HX4").Select
    Selection.Copy
    Range("HY4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C22),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C15))"
    Range("HY4").Select
    Selection.Copy
    Range("HZ4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",SUMIF('Extraction Stock France'!C[-275]:C[-274],'Ventes Détaillées'!RC4,'Extractions Ventes France'!C38),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C16))"
    Range("HZ4").Select
    Selection.Copy
    Range("IA4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1=""FRANCE"",0,SUMIF('Extractions Pays du Sud'!C12:C17,'Ventes Détaillées'!RC[-306],'Extractions Pays du Sud'!C17))"
    Range("IA5").Select

Sheets("Ventes Détaillées").Select
Dim DernLigne3 As Long
DernLigne3 = Range("A" & Rows.Count).End(xlUp).Row

Range("HW4").Select
Selection.AutoFill Destination:=Range("HW4:HW" & DernLigne3)

Range("HX4").Select
Selection.AutoFill Destination:=Range("HX4:HX" & DernLigne3)

Range("HY4").Select
Selection.AutoFill Destination:=Range("HY4:HY" & DernLigne3)

Range("HZ4").Select
Selection.AutoFill Destination:=Range("HZ4:HZ" & DernLigne3)

Range("IA4").Select
Selection.AutoFill Destination:=Range("IA4:IA" & DernLigne3)

Calculate

    Range("HW4:IA4").Select
    Range("IA4").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
'Fin semaine 28

Au niveau des colonnes il y en a une pré-remplie entre chaque semaine donc les colonnes remplies par la macro sont:

Semaine 27: HQ HR HS HT HU

Semaine 28: HW HX HY HZ IA

Je vous remercie d'avance pour votre aide.

Je suis à votre dispo pour toute information complémentaire.

Bonne journée

Le 20/10/2015 à 11:27

Bonjour,

Je trouve a-priori curieux de devoir passer chaque semaine en Case différent dans un Select Case dans la mesure où on fait le même type d'opérations à chaque fois...

Pour cerner ton problème, un fichier représentatif de l'organisation de tes données est nécessaire.

[Surtout pour moi ! car je ne cache pas que rebuté dès le départ de lire ta macro en raison des 'scories' de code enregistré qui la parsèment (abondance de Select, Selection, sans oublier les CutCopyMode=False) qui ne servent qu'à ralentir l'exécution et allonger le code, ainsi que l'insertion de formule par VBA (enregistré ou non) qui (sauf exception) n'est pas pas précisément optimal lorsqu'on peut faire autrement...]

Cordialement

Le 20/10/2015 à 14:44

Je vous remercie pour votre réponse rapide.

En effet, mes connaissances en VBA se résument pour l'instant principalement à de la compréhension du langage et j'ai encore besoin de l'enregistreur de macro pour avancer. Je sais que c'est loin d'être idéal et que ça rend les procédure lourde et difficilement lisible.

Vous trouverez le fichier dans le liens ci-dessous:

https://www.dropbox.com/s/vjkckvxuk92pa0p/Test_suivi%20des%20ventes2.xlsb?dl=0

J'ai essayé de l'épurer au maximum. La macro se trouve dans le module Macro_principale.

J'ai supprimé le contenu des colonnes superflues et je me suis arrêté à la semaine 29, le processus étant répété de la même manière jusqu'à la semaine 53.

Cependant je n'arrive pas à descendre sous les 300kio, c'est pourquoi je vous le transmet par ce biais. J'espère que c'est ok

Je vous remercie pour votre aide

Le 20/10/2015 à 16:07

Comme je l'ai dit, je ne suis pas partant pour décrypter ta macro, de façon à extraire les informations utiles : critères d'extraction, éléments à prélever et à traiter avant affectation...

Ce que je puis dire, c'est que chaque semaine occupant 6 colonnes, on peut aisément déterminer la position de chaque semaine :

si n est le numéro de la première colonne de la semaine 1, la semaine s débutera à la colonne : n+(s-1)*6 pour se terminer à : n+(s-1)*6+5. Ce qui permet d'envisager des boucles qui économiseront de gros volumes de code.

On peut donc certainement envisager une macro de taille raisonnable pouvant servir plusieurs semaines aussi bien qu'une seule avec le même code, éviter les formules en prélevant directement les données utiles (à partir de critères précis et éventuellement tri de la base) pour les constituer en tableau organisé pour les affecter au tableau-cible.

Je reste dans les généralités, car rechercher des précisions me demanderait considérablement plus de temps que d'écrire la macro ensuite !

Je te souhaite que quelqu'un veuille s'"amuser" à plonger dans ton fichier...

Cordialement

Le 20/10/2015 à 17:02

Je comprend tout à fait,

Le problème que je rencontre dans l'entreprise où je travail, en plus des connaissances VBA que je dois approfondir, c'est que les bases ne sont pas normées du tout et dispersées un peu partout dans leurs outils, cela m'oblige à aller chercher les information dans beaucoup d'endroits différents et je m'y perd un peu.

Je vais tenter de trouver la solution par moi même et je la posterai si j'y parvient.

Juste en enlevant tous les CutCopyMode = False, la macro se lance et les résultats obtenus sont bons donc je tiens a te remercier pour avoir soulevé ce point.

Je vais maintenant essayer d'obtenir un code plus simple, sans l'enregistreur.

Encore merci pour ta disponibilité,

Le 20/10/2015 à 17:53

Juste en enlevant tous les CutCopyMode = False, la macro se lance

C'est qu'il devait y en avoir de mal placés ! Cette commande a pour effet de vider le presse-papier (rempli par une commande Copy), ce qui ne sert à rien (car il se vide lors des opérations qui suivent) sauf dans 2 cas : si à la suite on ferme le classeur pour éviter le message demandant si on veut conserver le contenu du presse-papier, et pour éliminer (pour des raisons esthétiques) le scintillement autour de la zone copiée après collage.

L'enregistreur a la déplorable manie de l'inscrire systématiquement.

Pour les Select et Selection, ils sont logiques à l'enregistrement puisque c'est la reproduction des mouvements de l'utilisateur. Les faire sauter est assez facile :

PlageSource.Copy PlageDestination

(La plage source inclut éventuellement le nom de la feuille et définit l'ensemble de la zone copiée ; de même pour la plage destination mais où seule l'indication de la cellule supérieure gauche est nécessaire.)

Et pour un collage special :

PlageSource.Copy

PlageDestination.Paste xlPasteValues

(Là encore, l'enregistreur, allonge le code en répétant tous les paramètres par défaut, qui n'ont besoin d'être utilisés que si tu leur donnes une autre valeur....)

Bon courage pour la suite.

Le 20/10/2015 à 18:20

bonjour,

après un essai de décryptage de la macro voici une proposition

    Sheets("Extractions Ventes France").Select

    Dim numbersemaine As Integer
    numbersemaine = Range("B2")

    col = numbersemaine * 6 + 63 'numéro de colonne en fonction de la semaine

    Sheets("Ventes Détaillées").Select

    Cells(4, col).FormulaR1C1 = _
    "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C20),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C13))"
    Cells(4, col + 1).FormulaR1C1 = _
    "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C21),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C14))"
    Cells(4, col + 2).FormulaR1C1 = _
    "=IF(RC1=""FRANCE"",SUMIF('Extractions Ventes France'!C19:C22,'Ventes Détaillées'!RC4,'Extractions Ventes France'!C22),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C15))"
    Cells(4, col + 3).FormulaR1C1 = _
    "=IF(RC1=""FRANCE"",SUMIF('Extraction Stock France'!C[-275]:C[-274],'Ventes Détaillées'!RC4,'Extractions Ventes France'!C38),SUMIF('Extractions Pays du Sud'!C12:C17,RC1&'Ventes Détaillées'!RC7,'Extractions Pays du Sud'!C16))"
    Cells(4, col + 4).FormulaR1C1 = _
    "=IF(RC1=""FRANCE"",0,SUMIF('Extractions Pays du Sud'!C12:C17,'Ventes Détaillées'!RC[-306],'Extractions Pays du Sud'!C17))"
    Range("HU5").Select

    Sheets("Ventes Détaillées").Select
    Dim DernLigne2 As Long
    DernLigne2 = Range("A" & Rows.Count).End(xlUp).Row
    Cells(4, col).AutoFill Destination:=Range("HQ4:HQ" & DernLigne2)
    Cells(4, col + 1).AutoFill Destination:=Range("HQ4:HQ" & DernLigne2)
    Cells(4, col + 2).AutoFill Destination:=Range("HQ4:HQ" & DernLigne2)
    Cells(4, col + 3).AutoFill Destination:=Range("HQ4:HQ" & DernLigne2)
    Calculate

    Range(Cells(4, col), Cells(4, col + 3)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Le 21/10/2015 à 10:08

Bonjour,

Un énorme merci pour vos réponses !

La macro que vous m'avez envoyée fonctionne parfaitement.

J'ai simplement eu à modifier le Destination:=Range la partie Autofill par:

    Dim DernLigne2 As Long
    DernLigne2 = Range("A" & Rows.Count).End(xlUp).Row

    Cells(4, col).AutoFill Destination:=Range(Cells(4, col), Cells(DernLigne2, col))
    Cells(4, col + 1).AutoFill Destination:=Range(Cells(4, col + 1), Cells(DernLigne2, col + 1))
    Cells(4, col + 2).AutoFill Destination:=Range(Cells(4, col + 2), Cells(DernLigne2, col + 2))
    Cells(4, col + 3).AutoFill Destination:=Range(Cells(4, col + 3), Cells(DernLigne2, col + 3))
    Cells(4, col + 4).AutoFill Destination:=Range(Cells(4, col + 4), Cells(DernLigne2, col + 4))

Elle est vraiment nickel, très lisible et simple à comprendre.

Je vous remercie encore une fois.

Bonne journée à vous,

Le 21/10/2015 à 10:46

Tout s'arrange ! Tu as de la chance...

Bonne journée

Rechercher des sujets similaires à "select case procedure trop longue"