Formule évolutive VBA sur plusieurs pages

Salut à tous,

Un petit topic matinal!

Alors voila, j'ai créé ce gros fichiers pleins d'options que je vous joins JUSTE ICI:

Pour faire court, ce fichier gère l'enregistrement de diverses données de notre production et le suivie des remplacements des consommables.

Ma question concerne une maccro "cachée" (pas accessible depuis un bouton) du fichier que vous retrouverez dans le développer dans la section UserForm.

userform

Son objectif est simple. Nombre de mes collègues ici ne maîtrise absolument pas Excel. Comme il est possible qu'un jour un nouveau programme apparaisse dans notre processus de fabrication et que je souhaite éviter les voire toucher les feuilles cachés/programme, j'ai créé cette maccro qui ajoute dans l'onglet liste le nouveau programme, tri le tout, puis me créé des colonnes sur les pages concernés (Registre VAC et STS / En cours VAC et STS). Petit bémole, la formule qui calcule le temps totales d'utilisation du consommable...

formule

Cette formule tient compte des temps par programme sur la page "liste". Imaginons que l'on ajoute un nouveau programme, alors la formule contera une addition de plus. J'ai pensé utilisé mes différents compteurs qui me permettrait de pouvoir indiquer les positions des programmes sur les feuilles et dans ma liste. Mais de la à le transformer en une formule dans Excel... compliqué... Une idée? sinon tant pis, mes collègues feront cette opération manuellement...

Merci à vous et je suis dispo pour toutes vos questions/remarques!

Bonjour,

déjà ta formule peut être simplifiée en :

=SOMMEPROD(TRANSPOSE(F2:M2)*(Liste!$F$2:$F$9))

A partir de là il n'y a plus qu'à mettre des plages variables selon le nombre de données en Liste!F:F :

=SOMMEPROD(TRANSPOSE(DECALER($F2;;;;NB(Liste!$F:$F)))*DECALER(Liste!$F$2;;;NB(Liste!$F:$F)))

Bien que ce soit un Sommeprod() déjà matriciel, il faut la valider en matricielle avec Shift+Ctrl+Entrée

Ce qui en vba donne :

    Dim f As String, c As Range
    f = "=SUMPRODUCT(TRANSPOSE(OFFSET(RC6,,,,COUNT(Liste!C6)))*OFFSET(Liste!R2C6,,,COUNT(Liste!C6)))"
    With Sheets("STS en cours")
        For Each c In .[N2:N23]
            c.FormulaArray = f
        Next c
    End With

On est obligé de faire cellule par cellule et non la plage d'un coup à cause de la validation matricielle qui doit être individuelle.

J'ai fait sur une plage fixe, je te laisse compléter pour s'adapter au nombre de lignes nécessaire.

eric

Hum, j'avoue ne pas y arriver, je continue à essayer mais je risque de revenir vers toi. En tout cas, je comprends la logique de ta solution, j'ai pu comprendre comment placer la formule, mais j'ai du mal à savoir quoi mettre exactement. Pourquoi fait tu appelles à RC6 et R2C6? Désolé

Mais, j'vais trouver!! pas de soucis! déjà un grand merci!

(J'aurai dû mettre mon message suivant ici, pour les relecteurs, ne tenez pas compte de ce message)

AUTANT POUR MOI!!

J'avais bien compris mais j'ai fait une erreur, j'avais mis D sur liste au lieu de F...

Du coup ça fonctionne! me reste plus qu'à remplacer le F9 en F et une variable last et hop, ça devrait être bon!!

Excuse moi Eriiic, je me permet de revenir vers toi avant de clore ce topic.

La formule, c'est impec, j'ai réussit à l'adapter en fonction de la taille de ma liste. Mais, le problème c'est le compteur... Il ne cesse de me dire qu'il y a une erreur...

Alors j'ai transformé le for each ... in par juste un for comme cela:

Sub test()
 Dim f As String, c As Integer, last_list As Integer
 last_list = 9
 f = "=SUMPRODUCT(TRANSPOSE(OFFSET(F2;;;;COUNT(Liste!$F2:$F" & last_list & ")))*OFFSET(Liste!F2;;;COUNT(Liste!$F2:$F" & last_list & ")))"

                    MsgBox (f)    'la msgbox était juste un test pour savoir si j'avais la bonne formule

                    For c = 2 To 23
                        Cells(c, 14) = f
                    Next c
End Sub

Mais la encore... ça ne fonctionne pas.

Une idée? j'ai aussi fait sauter le formula array.

Merci encore!

CORRECTION

Sub test()

 Dim f As String, c As Integer, last_list As Integer
 last_list = 9

                    With Sheets("STS en cours")
                    For c = 2 To 23
                        f = "=SUMPRODUCT(TRANSPOSE(OFFSET(F" & c & ";;;;COUNT(Liste!$F2:$F" & last_list & ")))*OFFSET(Liste!$F$2;;;COUNT(Liste!$F2:$F" & last_list & ")))"
                        MsgBox (f)
                        Cells(c, 14).FormulaArray = f
                    Next c
                    End With

End Sub

J'en suis rendu ici mais ça ne fonctionne toujours pas :/

Tu trouves vraiment que ça ressemble à ce que je t'ai fourni ?

C'est quoi ce dim c as integer ?

En quoi il te gênait le For Each ?

j'ai aussi fait sauter le formula array.

Si tu changes plein de trucs, forcément ça fonctionne moins bien après.

Et ce n'est pas comme ça qu'on détermine le nombre de lignes utiles.

Pour mettre la formule sur le nombre de lignes utiles (je me base sur la colonne B) :

    Dim f As String, c As Range
    f = "=SUMPRODUCT(TRANSPOSE(OFFSET(RC6,,,,COUNT(Liste!C6)))*OFFSET(Liste!R2C6,,,COUNT(Liste!C6)))"
    With Sheets("STS en cours")
        For Each c In .[N2].Resize(.Cells(Rows.Count, "B").End(xlUp).Row - 1)
            c.FormulaArray = f
        Next c
    End With

Vu qu'il faut fournir la formule en R1C1 à vba du fait qu'elle est matricielle, si tu changes de colonne pour la formule met-toi en enregistrement de macro et tu revalides la formule en ligne 2. La chaine te sera livrée sur un plateau.

eric

Oups! Je ne connaissais aucune de tes formules je l'avoue! quant à travailler en matriciel, un mystère et j'avoue ne toujours pas comprendre tes formules n'y comment on obtient le bon résultat. J'en avais fait une interprétation en pensant que tu m'avais juste donné une direction alors j'ai adapté, j'obtenais le bon résultat à mon calcul ce qui m'a conforté! Désolé!

Je vais ré-essayer avec ta méthode!

quitte à être chiant (désolé!), j'avais une dernière question haha

pour le For each c in .[N2], la position N risque de bouger à l'ajout de chaque colonne. Comment faire en sorte qu'il vaille une valeur évolutive?

Merci!

Quand tu auras progressé tu comprendras qu'il y avait de quoi faire des bonds sur sa chaise...

Et pour progresser rien de mieux que F1 sur la fonction, la propriété ou la méthode que tu ne connais pas.

Avant d'y toucher bien sûr. Réfléchir avant d'agir.

Vu que je n'ai toujours pas mangé, je vais juste te donner les indications pour résoudre ton dernier problème.

Je vois 2 façon d'y arriver :

Méthode 1 :

- concaténer la formule f pour éditer les références R1C1 en fonction de la colonne où elle sera mise

simple à comprendre mais plus prise de tête dans sa mise en oeuvre

Méthode 2 à mon avis plus simple même si plus longue à décrire :

  • mettre dans f un copié-collé de la formule (ligne 2, en notation A1)
  • coller la formule en 1 fois sur la plage complète (très important le 'en 1 fois', pour que les références relatives ligne se mettent à jour correctement) avec .formulalocal
  • re-valider en matriciel les cellules individuellement avec :
 c.FormulaArray = c.Formula

Je te conseille de commencer par un F1 sur formula, formulaR1C1, formulaLocal, formulaR1C1Local, formulaArray

Tu auras un aperçu des outils à ta disposition et de leurs particularités.

Bien sûr les notions de relatif ligne/colonne sont très importantes ici.

A tout hasard vu que je ne connais pas ton niveau excel : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=62 Références Relatives & Absolues

eric

Ca marche, je vais étudier tout ça. J'ai déjà pu pas mal me renseigner sur OFFSET qui était déjà un bon point bloquant.

Je devrais pouvoir m'en tirer, je reviendrais avec une solution (si dieu le veut!) pour la présenter sur ce Topic dans l'espoir qu'il puisse servir à quelqu'un un jour!

En tout cas merci pour ton aide! Franchement au top!

Et voici le genre que je n'ai pas apprécié non plus

Je suis un brusque parfois, faut s'y faire...

Termine ta version d'abord, c'est comme ça qu'on apprend le plus.

Voici comment je voyais le truc :

    Dim f As String, nbPrg As Long, pl As Range, c As Range
    f = "=SOMMEPROD(TRANSPOSE(DECALER($F2;;;;NB(Liste!$F:$F)))*DECALER(Liste!$F$2;;;NB(Liste!$F:$F)))"
    nbPrg = Sheets("Liste").Cells(Rows.Count, "F").End(xlUp).Row - 1
    With Sheets("STS en cours")
        Set pl = .[F2].Offset(, nbPrg).Resize(.Cells(Rows.Count, "B").End(xlUp).Row - 1)
    End With
    pl.FormulaLocal = f ' validation 'normale' pour avoir les réf correctes sur toute la plage
    For Each c In pl
        c.FormulaArray = c.Formula ' revalidation en matricielle vu que FormulaA1Array n'existe pas
    Next c
End Sub

Vite fait et pas vraiment testé, il parait qu'il y a un truc à la télé à 16h. Tu auras peut-être des adaptions à faire mais la philosophie de la méthode y est.

eric

Bon, je ne regarde pas de suite pour pas me laisser influencer mais merci!

ça a l'air top!

Je tiens peut être un truc aussi mais c'est bidouillé...

Salut eriic!

C'est une victoire, c'est tout bon!

J'ai fait une première technique pas mal mais lourde en utilisant le R1C1 avec pour numéro une variable qui prenait la dernière colonne du tableau -2. J'étais ainsi sur de toujours tomber sur l'ensemble des références de programme. Ensuite, j'utilisais formulaArray et le tour était joué.

Puis après, j'ai testé la tienne que j'ai conservée.

Je vous met ici le cas pour la page de "VAC en COURS" (pour ceux qui suivraient le dossier). last_liste correspond au nombre de ligne de programme sur la page "Liste" -1.

f = "=SUMPRODUCT(TRANSPOSE(OFFSET($F2;;;;COUNT(Liste!$F:$F)))*OFFSET(Liste!$F$2;;;COUNT(Liste!$F:$F)))"
                        With Sheets("VAC en cours")
                            Set pl1 = .[f2].Offset(, last_liste - 1).Resize(.Cells(Rows.Count, "B").End(xlUp).Row - 1)
                        End With
                            pl1.FormulaLocal = f ' validation 'normale' pour avoir les réf correctes sur toute la plage
                        For Each c In pl1
                            c.FormulaArray = c.Formula ' revalidation en matricielle vu que FormulaA1Array n'existe pas
                        Next c

En tout cas, je n'aurai JAMAIS eu cette idée! Mais je suis super content pour ces nouvelles fonctions! R1C1 c'est super pratique! Puis ton astuce OFFSET into RESIZE pour venir y mettre la formule à coup sur dans la colonne voulue... Chapeau!

Bref, une première expérience matricielle intéressante!

Bonjour,

j'ai allégé mes posts aussi

Bonne continuation et bon apprentissage.

eric

PS :

Si tu ne connais pas, un utilitaire qui va t'aider à indenter correctement. Avoir les boucles bien alignées ça aide bien au débogage :

Est toujours valide et ne s'arrête pas à 2003 contrairement à ce qui est indiqué.

Rechercher des sujets similaires à "formule evolutive vba pages"