Prolongement de formules (VBA)

[Arnaud dit Citro]

Bonjour à tous,

A partir du code que m'ont donné Staple1600 et Frangy (que je remercie de nouveau) sur ce post : https://www.excel-downloads.com/thr...-selon-criteres-macro.20031363/#post-20231191 et que j'ai adapté à plusieurs feuilles sans aucun problème, j'ai créé ce code :

With Sheets("Statistiques")
        .Rows("6:6").Insert shift:=xlDown
        ColSta = Application.Max(.Cells(6, Columns.Count).End(xlToLeft).Column, 1) + 1
        .Cells(6, ColSta).Formula = "=BdD_Vendeur!A" & Fve
        If ColSta = 2 Then
            .Cells(6, ColSta + 1).Formula = "=IF(B6=Devis!Y$7,COUNTIFS(Devis!Y$8:Devis!Y$1000,1),0)"
        Else
            .Cells(6, 2).Resize(1, 2).Copy .Cells(6, ColSta)
            .Cells(6, 2).Resize(1, 2).Copy .Cells(6, ColSta + 1)
            Application.CutCopyMode = False
        End If
    End With

Ce code fonctionne parfaitement, B6 devient bien B7 à la création du second vendeur et B8 au suivant. Mais (parce que forcément il y a un mais), il faudrait qu'à la création du second vendeur et des suivants, la formule concernant la feuille "Devis" avance de 4 colonnes, c'est à dire :

"=IF(B6=Devis!Y$7,COUNTIFS(Devis!Y$8:Devis!Y$1000,1),0)"

deviendrait

"=IF(B7=Devis!ACY$7,COUNTIFS(Devis!AC$8:Devis!AC$1000,1),0)"

pour le second vendeur et

"=IF(B8=Devis!AG$7,COUNTIFS(Devis!AG$8:Devis!AG$1000,1),0)"

pour le suivant et ainsi de suite.
Si toutefois cela est possible.

Bon dimanche à tous

Arnaud

 

[Dranreb]

Bonjour.
Comment sait-on que c'est le nième vendeur ?
Supposons que c'est une variable NV, alors

CX = "C" & 4 * (NV - 1) + 25
.Cells(6, ColSta + 1).FormulaR1C1 = "=IF(RC2=Devis!R7" & CX &,COUNTIFS(Devis!R8" & CX & ":Devis!R1000" & CX &",1),0)"

Pénible ces classeur non joints …

 

[Arnaud dit Citro]

Bonsoir,

Je vais essayer dans la soirée d'épurer le fichier et de le joindre.

Bonne soirée

 

[Arnaud dit Citro]

Bonsoir,

 CX = "C" & 4 * (NV - 1) + 25 .Cells(6, ColSta + 1).FormulaR1C1 = "=IF(RC2=Devis!R7" & CX &,COUNTIFS(Devis!R8" & CX & "Devis!R1000" & CX &",1),0)"

Je viens d'essayer, ça recule de 4 colonnes et ça n'augmente pas à la création du vendeur.

Les vendeurs se créent via un formulaire, ils sont intégrés automatiquement dans les devis (grâce au code de Frangy) sur 4 colonnes chacun avec des formules.

Bonne soirée à tous

Arnaud

 

[Dranreb]

Bonsoir.
Je ne comprends pas. Vous insérez toujours en ligne 6, alors ce sont les autres formule en dessous qui doivent se transformer ?
Vous auriez peut être intéret à mettre une formule commune à base de DECALER utilisant LIGNE dans ce cas.

 

[Dranreb]

Ou alors si les colonnes dans la feuille devis sont en ordre inverse, Insérez 4 colonnes: Les formules déjà en place se décaleront automatiquement. Il est préférable de mettre des références de colonnes absolues $Y$7

 

[Arnaud dit Citro]

Effectivement, j'insère en ligne 6. Le premier créé passe en ligne 7 mais conserve sa formule sauf B6 qui passe en B7 (et cela est nécessaire), le reste ne change pas.
Lors de la création du second il faut que Y7 devienne AC7, pour le suivant AG7 et ainsi de suite.

Vous pouvez faire des essais sur le fichier, lors de la création d'un vendeur, 4 colonnes sont créées dans la feuille devis et des lignes dans la feuille statistiques.

 

[Dranreb]

Apparemment elle ne sont pas créées par insertion de colonnes, donc les formules en place ne suivent pas.

 

[Arnaud dit Citro]

Les codes sont dans l'userform1, et dans la feuille devis ce n'est pas une insertion mais un remplissage de colonne via le code de l'UF1

 

[Dranreb]

Oui, donc remplacez ça par une insertion de colonnes. On ne va pas faire un traitement ou des formules compliqués pour ça alors qu'Excel corrige automatiquement et partout les références aux cellules décalées suite à des insertions en amont.

 

[Arnaud dit Citro]

Je crois que j'ai trouvé : j'avais pas percuté lorsque vous me parliez de la variable NV pour un nouveau vendeur, mais un peu plus haut dans le code de l'UF1, le nouveau vendeur est Fve, et à partir du code que vous m'avez donné, j'ai fait ça :

 With Sheets("Statistiques")
        .Rows("6:6").Insert shift:=xlDown
        ColSta = Application.Max(.Cells(6, Columns.Count).End(xlToLeft).Column, 1) + 1
        .Cells(6, ColSta).Formula = "=BdD_Vendeur!A" & Fve
        CX = "C" & 4 * (Fve - 1) + 13
        If ColSta = 2 Then
            .Cells(6, ColSta + 1).FormulaR1C1 = "=IF(RC2=Devis!R7" & CX & ",COUNTIFS(Devis!R8" & CX & ":Devis!R1000" & CX & ",1),0)"
        Else
            .Cells(6, 2).Resize(1, 2).Copy .Cells(6, ColSta)
            .Cells(6, 2).Resize(1, 2).Copy .Cells(6, ColSta + 1)
            Application.CutCopyMode = False
        End If
    End With

Je ne suis pas sur de comprendre pourquoi il fallait +13 mais cela semble fonctionner.

Je vais faire demain soir des tests et je confirmerai (ou infirmerai) que cela fonctionne correctement.

Merci de votre aide

Bonne nuit

Arnaud

 

[Dranreb]

Ce n'est pas 13 mais 25, le numéro de la colonne Y.

 

[Arnaud dit Citro]

je sais bien mais 25 ça ne fonctionne pas, j'y ai été à taton jusque trouver la bonne correspondance et ça a donné 13

 

[Dranreb]

Ça ne peut pas donner une référence à la colonne Y quand Fve = 1
Donc j'en conclus que ça ne marche que pour le nombre de vendeur que vous avez actuellement.
Et d'ailleurs, vu que vous refuser d'insérer des colonnes pour avoir le moins de corrections à faire, ce n'est pas la ligne insérée qu'il faut toucher, celle ci c'est toujours Y si j'ai bien compris, mais toutes les suivantes dans une boucle.

 

[Arnaud dit Citro]

Bonsoir,

Je viens de faire des essais, j'ai créé une bonne douzaine de vendeur, et cela fonctionne parfaitement. Je ne sais pas pourquoi et je ne comprend pas pourquoi mais cela fonctionne.... Faites un essai en remplaçant le code.

Donc j'en conclus que ça ne marche que pour le nombre de vendeur que vous avez actuellement.

En faisant les tests ce soir, je suis reparti de zéro vendeur, et cela a bien fonctionné.

Et d'ailleurs, vu que vous refuser d'insérer des colonnes pour avoir le moins de corrections à faire, ce n'est pas la ligne insérée qu'il faut toucher, celle ci c'est toujours Y si j'ai bien compris, mais toutes les suivantes dans une boucle.

Désolé, je ne comprend pas ce que vous voulez dire.

Bonne soirée

Arnaud