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.
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...
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 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é.