Formule évolutive VBA sur plusieurs pages Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 08:50

Salut à tous,

Un petit topic matinal!

Alors voila, j'ai créé ce gros fichiers pleins d'options que je vous joins JUSTE ICI:
test pour excelpratique.xlsm
(266.56 Kio) Téléchargé 8 fois
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.png
UserForm.png (3.77 Kio) Vu 204 fois
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.png
formule.png (6.32 Kio) Vu 204 fois
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!
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'334
Appréciations reçues : 393
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 6 juillet 2018, 10:06

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
1 membre du forum aime ce message.
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 10:46

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)
Modifié en dernier par grexcel le 6 juillet 2018, 10:49, modifié 1 fois.
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 10:48

AUTANT POUR MOI!!

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

Du coup ça fonctionne! me reste plus qu'à remplacer le F9 en F et une variable last et hop, ça devrait être bon!!
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 11:10

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 :/
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'334
Appréciations reçues : 393
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 6 juillet 2018, 13:08

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
Modifié en dernier par eriiic le 9 juillet 2018, 18:25, modifié 1 fois.
1 membre du forum aime ce message.
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 13:19

Oups! :lole: 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!
Modifié en dernier par grexcel le 9 juillet 2018, 12:02, modifié 1 fois.
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 13:31

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! :ange:
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'334
Appréciations reçues : 393
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 6 juillet 2018, 14:52

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/smar ... ?itemid=62 Références Relatives & Absolues
eric
Modifié en dernier par eriiic le 9 juillet 2018, 18:26, modifié 1 fois.
1 membre du forum aime ce message.
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
g
grexcel
Membre habitué
Membre habitué
Messages : 85
Appréciations reçues : 4
Inscrit le : 18 octobre 2017
Version d'Excel : 2016

Message par grexcel » 6 juillet 2018, 15:06

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!
Modifié en dernier par grexcel le 9 juillet 2018, 12:03, modifié 1 fois.
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message