Formule macro
Bonjour,
J'ai un problème avec mon fichier. J'ai deux bases de données dont j'aimerais que l'une complète l'autre automatiquement.
J'ai essayé de créer une macro mais la macro calcule une seule donnée puis EXCEL plante sans avoir terminé d'exécuter la macro. Et quand je rajoute une condition il y a un bug au niveau de Selection.AutoFill Destination:=Range("H2:H125"), Type:=xlFillDefault.
Est ce que quelqu'un pourrait m'aider ? Je suis débutante dans ce genre de programme.
Vous trouverez mon fichier en pièce jointe pour mieux comprendre.
Merci d'avance, toute aide est la bienvenue
Salut et bienvenue sur le Forum,
On ne peut pas vraiment dire que l’on voit où tu veux en venir avec tes macros. Peux-tu expliquer avec des mots simples mais avec des références précises aux objets Excel ce que tu désires réaliser ?
Cordialement
Salut,
Merci Yvouille
Oui je me disais aussi que mes macros étaient peut-etre embrouillés... Alors je vais essayer d'expliquer avec des mots simples.
J'aimerais compléter ma feuille "Data 2" avec les données de "Data 1". En effet il s'agit de planning avec des jours consommés depuis janvier à février. Certains plannings ont des jours consommés et d'autres non, certains ont plusieurs jours de consommés mais dans différentes équipes ce qui fait qu'il faut faire la somme de ces jours qui concernent le même planning.
Donc j'ai trouvé une matrice qui me fait la somme de ces différentes équipes pour un même planning.
Je voudrais que la macro applique la matrice INDEX+EQUIV. sur tous les plannings.
Mais pour les autres plannings en couleur un SOMME.SI.ENS. donc j'aimerais qu'une macro puisse appliquer cette fonction sur le reste des plannings.
Et pour les plannings qui n'ont pas de conso un #N/A va s'afficher et j'aimerais que la macro change en "0" ces cases d'erreurs.
Voilà j'espère avoir été assez claire.
Merci pour l'aide (si c'est possible j'ai l'impression de faire un casse-tête chinois).
Aie, tu me fais mal à la tête avec tes explications
Ne peux-tu tous d'abord pas réduire ton fichier à 10 ou 20 lignes représentatives ? Ce serait beaucoup plus simple à nous donner des exemples.
Ensuite prends un problème après l'autre. Pour ton premier problème, sur la base de ton fichier réduit, que veux-tu réaliser ? N'essaie pas de nous expliquer comment tu penses qu'il faut résoudre le problème, explique-le-nous uniquement.
A te relire, mais seulement demain, car sous mes latitudes, c'est l'heure d'aller se coucher
Désolé pour le mal de tête, bon j'ai réduit mon fichier et je vais faire étape par étapes comme tu m'as dit.
Donc la 1ère chose que je voudrais c'est que :
- la macro puisse appliquer ma formule matricielle à tous les plannings concernés automatiquement.
Oui moi aussi je vais dormir, merci encore de prendre du temps pour mon casse-tête
A demain
Sam
Re,
Contrairement à ce que je t’ai demandé, tu ne m’as pas dit quel résultat tu désirais par des mots simples, tu m’as simplement indiqué d’une manière détournée que ta formule ne fonctionnais pas comme tu le pensais.
Cependant, selon moi, ta formule fonctionne à la base car tu as de la chance. Je m’explique : tu as inscrit une formule en H2 de la feuille Data 2 et tu l’as incrémentée vers le bas. Comme tu n’as pas utilisé de références absolues (à l’aide de la touche F4), ton incrémentation concernant le tableau de la feuille Data1 est incrémenté au lieu d’être figé, mais comme tes références sont dans l’ordre, ça fonctionne quand même. Mais si tes références sur la feuille Data1 n’étaient pas dans l’ordre – comme j’ai déplacé la ligne marquée en bleu dans mon fichier - ça ne jouerait plus (en H16 de la feuille Data 2, ta formule ne trouve plus la bonne valeur).
La solution est d’utiliser les références absolues (par exemple 'Data 1'!$I$2:$I$73 au lieu de 'Data 1'!I2:I73, modification effectuée par la touche F4), comme je l’ai fait pour ma formule en J2 de la feuille Data 2, incrémentée vers le bas. Tu vois alors que les références de la feuille Data 1 sont figées d’une cellule à une autre (J3, J4, etc.) et que la bonne valeur pour la ligne bleue en J16 est trouvée.
Maintenant je ne sais pas si tu penses que ta formule ne fonctionne pas car il y a des résultats ‘#N/A’ ?? Mais ces signes indiquant que les valeurs recherchées n’ont pas été trouvées sont tout à fait normaux puisque les valeurs recherchées n’existent pas sur la feuille Data 1. Un deuxième contrôle dans mes cellules en vert montre bien que ces références composées de données prises dans 3 colonnes n’existent pas toujours sur la feuille Data 1.
Ces résultats ‘#N/A’ pourraient être cachés si désiré.
Comme tu m’as fourni un fichier réduit, il se pourrait que toutes les références recherchées soient à disposition sur le fichier non réduit. Dans ce cas-là, l’explication de ton problème serait uniquement la non utilisation des références absolues dans ta formule.
A propos de ta formule, il est inutile d’y placer des références 'Data 2'! si tu es justement sur la feuille Data 2. Dans ma formule en J2, j’ai simplifié cela.
Si ton problème est résolu, on peut passer au suivant.
Amicalement.
NB : En relisant tes messages, je vois que tu demandais une solution par macro. Si tu m'avais expliqué ce que tu désirais comme résultat au lieu de me fournir cette formule incorrecte, je n'aurais pas foncé tête baissée dans la fausse direction. Si tu veux que je travaille maintenant sur une solution par macro, fourni-moi le début de ta macro à corriger ou à compléter et indique-moi clairement si tu désires placer au travers d'une macro une formule sur ta feuille Excel ou si tu veux y placer un résultat uniquement.
Re,
Merci pour tes explications alors j'ai réessayer ta formule avec les cellules figées et tout fonctionne comme je veux
Concernant les #N/A c'est normal certaines données ne sont pas dans mon fichier c'est pour cela qu'il ne les trouve pas.
Oui effectivement je me suis mal exprimée (encore une fois
Alors j'aimerais que la macro puisse y appliquer mes deux formules à tous mes plannings en fonction de certaines conditions.
J'ai remis mon fichier avec les deux formules.
Et la macro que j'ai commencé de créer est vraiment brouillon...
La voici :
Sub Macro2()
'
'Application de la formule INDEX EQUIV à tous les plannings
Selection.FormulaArray = _
"=INDEX('Data 1'!R2C9:R264C9, MATCH(RC[-5]&RC[-3]&RC[-1],'Data 1'!R2C3:R264C3&'Data 1'!R2C5:R264C5&'Data 1'!R2C8:R264C8,0))"
'Si colonne B = "PJ" mettre la formule SOMME.SI.ENS.
If Column = PJ Then
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Data 1'!R2C9:R264C9,'Data 1'!R2C2:R264C2,RC[-6],'Data 1'!R2C5:R264C5,RC[-3],'Data 1'!R2C8:R264C8,'Data 2'!RC[-1])"
'Si planning en couleur mettre fonction SOMME.SI.ENS.
If testcouleur = cellulecouleur Then
Range("H18").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Data 1'!R2C9:R264C9,'Data 1'!R2C3:R264C3,RC[-5],'Data 1'!R2C5:R264C5,RC[-3],'Data 1'!R2C8:R264C8,'Data 2'!RC[-1])"
End If
End Sub
Par contre je n'arrive pas à l'exécuter alors que j'ai activé les macros de mon classeur...
Merci pour ton temps, j'espère que je suis plus claire...
Mon fichier est en pièce jointe.
Amicalement
Salut,
Désolé, mais non, tes explications ne sont pas très claires. Tu dois absolument faire référence aux noms des objets Excel (Feuilles, cellules, plages de cellules, colonnes, etc.) au lieu de parler ton langage connu de toi seule. Lorsque tu parles de ‘planning’, je ne sais absolument pas de quoi tu parles, si ce n’est que je devine qu’il pourrait peut-être s’agir des deux feuilles ‘Data 2 (2)’ et ‘Data 2’.
Comme j’ai beaucoup de questions ci-dessous, je les ai numérotées afin de faciliter tes réponses.
Question 1) Maintenant est-ce que j’ai bien compris que les formules placées dans la colonne H de la feuille ‘Data 2 (2)’ sont des exemples de ce que tu voudrais obtenir ? Q 2) Si oui, j’ai alors compris qu’en fonction du libellé de la colonne B tu désirais telle ou telle formule dans la colonne H ? Peux-tu me dire avec des mots quelles sont ces conditions ?
Q 3) Tu parles de deux formules, mais il s’agit bien soit de l’exemple placé dans la cellule H2 de ta feuille ‘Data 2 (2)’, soit dans la cellule H3 de cette même feuille ?? Q 4) Donc je ne m’occupe pas du tout de la formule placée dans la cellule J21 et suivante de cette feuille ?
Encore une autre question : Q 5) Si j’ai bien compris, tu vas rajouter des feuilles de planning [Q 6) sur le modèle exact des feuilles ‘Data 2 (2)’ et ‘Data 2’ ?] dans ce fichier et vouloir que la macro trouve toujours – pour la colonne H – s’il faut appliquer l’une ou l’autre formule. Dans tous les cas – que ce soit avec ton fichier comme il se présente actuellement ou avec des feuilles ajoutées – comment veux-tu déclencher la macro ? Directement depuis l’explorateur VBA ? Par l’intermédiaire d’un bouton ? Mais alors où penses-tu placer ce bouton ? Si tu n’en as aucune idée, attends-tu une proposition ?
Q 7) Peux-tu aussi me confirmer que la base de données pour toutes les formules est toujours la feuille ‘Data 1’ ?
Q 8) Dans la cellule H2 de ta feuille ‘Data 2 (2)’, tu as une formule dont un passage fait référence à cette propre feuille ‘Data 2 (2)’. Peux-tu me confirmer qu’il s’agit d’une erreur et que chaque formule placée dans différentes feuilles doit faire référence à cette feuille et non pas toujours à la feuille ‘Data 2 (2)’ ?
Tu as indiqué ton fil comme ‘Résolu’ et on voit maintenant un petit V vert à côté du titre de ton fil. Si ton sujet n’est pas résolu, il faudrait mieux ne pas l’indiquer comme tel, c’est notamment plus facile pour moi, lorsque je regarde la liste de mes sujets traités, si tel ou tel fil est liquidé ou non. Tu peux alors indiquer que ton fil n’est pas résolu et cliquant sur le petit V rouge dans l’un de tes messages. Par la suite, lorsque ton fil sera vraiment résolu, il faudra alors à nouveau cliquer sur le petit V vert.
A te relire.
Salut,
J'ai essayé de répondre du mieux possible à tes questions s'il manque des précisions, n'hésite pas j'ai un peu de mal à m'expliquer en langage EXCEL..
Question 1) Maintenant est-ce que j’ai bien compris que les formules placées dans la colonne H de la feuille ‘Data 2 (2)’ sont des exemples de ce que tu voudrais obtenir ?
Oui les formules de la colonne H de la feuille ‘Data 2 (2)’ sont des exemples de ce que je voudrais obtenir dans la feuille ‘Data 2’.
Q 2) Si oui, j’ai alors compris qu’en fonction du libellé de la colonne B tu désirais telle ou telle formule dans la colonne H ? Peux-tu me dire avec des mots quelles sont ces conditions ?
Non, les formules changent en fonction du libellé de la colonne C :
Si dans la colonne C le libellé (je viens de voir que seule la colonne D a des libellés en couleur…) est en couleur cela veut dire qu’il y a plusieurs valeurs à prendre en compte dans la feuille ‘Data 1’ car elles ont le même libellé (en colonne C) et les critères de distinction seront dans la colonne C, E et G de la feuille ‘Data 2’
Si dans la colonne C il ne trouve pas de valeurs alors il faut prendre en compte le libellé de la colonne B avec les critères se trouvant dans les colonnes E et G de la feuille ‘Data 2’.
Enfin si l’une et l’autre de ces conditions ne s’applique pas au libellé de la colonne C alors il faut prendre en compte les critères des colonnes C, E et G de la feuille ‘Data 2’.
Q 3) Tu parles de deux formules, mais il s’agit bien soit de l’exemple placé dans la cellule H2 de ta feuille ‘Data 2 (2)’, soit dans la cellule H3 de cette même feuille ?? Q 4) Donc je ne m’occupe pas du tout de la formule placée dans la cellule J21 et suivante de cette feuille ?
Oui je parle bien des exemples de formules en H2 et H3 de la feuille Data 2 (2). Mais si tu as d’autres formules plus simples en fonction de mes conditions à me proposer je veux bien modifier les miennes. Non en fait la formule en J21 c’était pour montrer la différence de résultats que je trouve en fonction des formules utilisées. Donc non pas la peine de s’en occuper.
Encore une autre question : Q 5) Si j’ai bien compris, tu vas rajouter des feuilles de planning
[Q 6) sur le modèle exact des feuilles ‘Data 2 (2)’ et ‘Data 2’ ?] dans ce fichier et vouloir que la macro trouve toujours – pour la colonne H – s’il faut appliquer l’une ou l’autre formule. Dans tous les cas – que ce soit avec ton fichier comme il se présente actuellement ou avec des feuilles ajoutées – comment veux-tu déclencher la macro ? Directement depuis l’explorateur VBA ? Par l’intermédiaire d’un bouton ? Mais alors où penses-tu placer ce bouton ? Si tu n’en as aucune idée, attends-tu une proposition ?
Non au fur et à mesure je vais rajouter des lignes dans la feuille ‘Data 1’ sur le modèle de la feuille ‘Data1’.
Oui j’aimerais que la macro trouve pour la colonne H l’une ou l’autre formule. Et j’aimerais que la macro se déclenche après l’ajout de lignes dans la feuille ‘Data 1’. J’avais pensé à mettre un bouton dans la feuille ‘Data 1 ‘ après les colonnes I. Si tu as une meilleure solution à me proposer je suis preneuse .
Q 7) Peux-tu aussi me confirmer que la base de données pour toutes les formules est toujours la feuille ‘Data 1’ ?
Oui la base de données sera toujours la feuille ‘Data 1’
Q8) Dans la cellule H2 de ta feuille ‘Data 2 (2)’, tu as une formule dont un passage fait référence à cette propre feuille ‘Data 2 (2)’. Peux-tu me confirmer qu’il s’agit d’une erreur et que chaque formule placée dans différentes feuilles doit faire référence à cette feuille et non pas toujours à la feuille ‘Data 2 (2)’ ?
Ah oui c’est une erreur j’ai copié la feuille Data 2 et du coup la référence a changé. Mais chaque formule doit faire référence à la feuille ‘Data 1’.
J’avais mis résolu pour la formule que tu m’avais corrigé je ne pensais pas que ça l’appliquerais à tout le sujet. Mais c’est bon j’ai décoché le petit V vert.
Sam (je vais te remercier mille fois mais j'apprécie ton aide
Salut,
Je suis peut-être un peu ‘’dur à la comprenette’’, mais d’un autre côté je pense que ton fichier est un peu emberlificoté.
Il me semble que j’ai bien compris tes réponses à mes questions à l’exception de la question 2.
Peux-tu alors pour chacun des cas que tu mentionnes dans ta réponse me fournir un exemple précis ?
Sam_P a écrit :Non, les formules changent en fonction du libellé de la colonne C :
Si dans la colonne C le libellé (je viens de voir que seule la colonne D a des libellés en couleur…) est en couleur cela veut dire qu’il y a plusieurs valeurs à prendre en compte dans la feuille ‘Data 1’ car elles ont le même libellé (en colonne C) et les critères de distinction seront dans la colonne C, E et G de la feuille ‘Data 2’
A quelles lignes de ton fichier puis-je trouver une telle constellation ? Merci d’utiliser les références aux objets Excel afin de répondre.
Sam_P a écrit :Si dans la colonne C il ne trouve pas de valeurs alors il faut prendre en compte le libellé de la colonne B avec les critères se trouvant dans les colonnes E et G de la feuille ‘Data 2’.
Merci de me fournir les références exactes d’un exemple concerné par un tel cas.
Sam_P a écrit :Enfin si l’une et l’autre de ces conditions ne s’applique pas au libellé de la colonne C alors il faut prendre en compte les critères des colonnes C, E et G de la feuille ‘Data 2’.
Idem pour cette troisième variante.
Si ton fichier n’inclut pas de tels exemples, merci de me fournir un nouveau fichier en comportant.
Ca va assez long, mais je suis persuadé que nous y arriverons. A toi de voir si tu as la patience de m’expliquer ton problème et d’attendre la solution
Salut,
Lol non je ne pense pas c'est mon fichier qui est assez "emberlificoté" comme tu dis ou sinon c'est moi qui n'arrive pas à avoir une vue simplifier de ce fichier...
Oui je t'enverrais tous ses exemples en fin de journée, pas de problème je suis patiente et j'aime apprendre alors on prendra le temps qu'il faut
A plus tard
Sam
Je ne sais pas si j'aurai accès à un ordinateur ces quelques prochains jours, donc si jamais tu auras une réponse au plus tard vers le début avril. Mais j'espère quand même avant. Je me souhaite un bon séjour en France - oh pas très loin, à la Rue du Centenaire à Ambilly, à 100 mètres de la Suisse - et te souhaite d'excellentes journées jusque là.
D'accord, aucun soucis !
Je te souhaite un bon séjour chez nous alors
Et à dans quelques jours
Sam
Salut,
Alors à force de chercher, j’ai pu simplifier mes conditions : les formules dépendront du fait que la colonne C soit en couleur ou non dans la feuille ‘Data 2’
- Si dans la colonne D le libellé n’est pas en couleur alors il faut prendre en compte les critères des colonnes C, E et G de la feuille ‘Data 2’. Exemple en H2
- Si la colonne D le libellé est en couleur alors on prend en compte les critères des colonnes B, E et G de la feuille ‘Data 2’. Exemple en H31
A plus tard
Salut,
Mes amis m’ont prêté un ordinateur et j’en ai profité durant leur sieste.
Je ne suis pas certain d’avoir tout compris, mais regarde le fichier ci-joint.
Lorsque tu sélectionnes la feuille ‘Data2’, une macro événementielle est déclenchée et elle passe en revue toutes les lignes de cette feuille. Si la ligne à une couleur de fond quelconque, une formule est inscrite en colonne H, autrement c’est une autre formule.
Est-ce que ça va dans le bon sens ?
Cordialement.
Salut,
Désolée pour ma réponse tardive j'étais en période de partiels..
Alors j'ai regardé ta formule c'est parfait ça fonctionne comme je veux
J'ai une dernière question par contre, est ce que je pourrais modifier les données de la feuille "Data 1" sans que la formule ne soit modifiée ?
Sam
Salut,
Je remarque à l’instant que j’ai oublié d’effacer les modules 1 et 2 dans l’explorateur VBA ; tu peux les effacer sans autre chez toi ; ce sont des modules provisoires créés lors de l’enregistrement de macros afin d’obtenir les bonnes formules à mettre dans le code de la ‘’Feuil2(Data2)’’.
Dans ce code de la ‘’Feuil2(Data2)’’, tu as la macro nécessaire à l’actualisation de la Feuil2 elle-même et j’ai placé plusieurs instructions R1000 qui font que les formules inscrites sur ta feuille Excel ‘’Data2’’ fassent références à la plage 'Data 1'!$I$2:$I$1000. Donc, tant que tu n’as pas plus de 1000 lignes sur la feuille ‘’Data1’’, tu pourrais ne rien changer.
Sinon on pourrait placer une instruction dans la macro qui recherche la dernière ligne de la feuille 1 et qui permettrait dans les formules de faire référence exactement à la plage de la feuille ‘’Data1’’ en tenant compte de ce nombre de lignes.
Ce ne serait pas si compliqué de le faire. Veux-tu que je m’y lance ?
Ou alors tu changes toi la macro en remplaçant les R1000 par des chiffres plus grands qui couvriraient d’une manière certaine le nombre de lignes maximum possible.
A te relire.
Oui j'ai vu mais c'est pas grave, ça me permet de mieux comprendre
Alors oui je voudrais que ça fasse plus de 1000 lignes, donc je vais écrire un nombre plus grand dans ta formule, mais jusqu'à combien de lignes peut-on aller ?
Je vais essayer de le faire seule et je reviens vers toi si j'ai un problème. De plus j'aimerais rajouter un autre critère qui se trouverait dans la colonne "I". Puis-je faire un enregistrement de ma formule voulue et la remplacer par la tienne ?
(je vais faire quelques tests pour voir si j'arrive à me débrouiller
Cordialement,
Sam
Sam_P a écrit :mais jusqu'à combien de lignes peut-on aller ?
Jusqu'à 1 048 576, pas une de plus
Ah j'ai de la marge ahah
Alors j'ai essayé de refaire ta macro évènementielle avec mon fichier de 3000 lignes mais il ne l'éxécute pas, pourtant j'ai recopié la même chose j'ai juste mis une nouvelle formule...
Voici ce que j'ai mis :
Private Sub Worksheet()
Dim i As Integer, j As Byte, DerLig As Integer
Application.ScreenUpdating = False
DerLig = Range("A" & Rows.Count).End(x1Up).Row
For i = 2 To DerLig
On Error Resume Next
j = Range("C" & i).Interior.ColorIndex
If j = 0 Then
Range("H" & i).FormulaR1C1 = "=SUMIFS('Data 1'!R2C9:R5000C9,'Data 1'!R2C3:R5000C3,RC[-5],'Data 1'!R2C5:R5000C5,RC[-3],'Data 1'!R2C8:R5000C8,RC[-1],'Data 1'!R2C10:R5000C10,RC[1])"
Else
Range("H" & i).FormulaR1C1 = "=SUMIFS('Data 1'!R2C9:R5000C9,'Data 1'!R2C3:R5000C2,RC[-6],'Data 1'!R2C5:R5000C5,RC[-3],'Data 1'!R2C8:R5000C8,RC[-1],'Data 1'!R2C10:R5000C10,RC[1])"
End If
j = 0
Next i
End Sub
Il trouve une erreur à la ligne "DerLig = Range ("A"...)" il me dit "erreur d'exécution 1004 : erreur définie par l'application ou l'objet.
Ou tu préfères voir mon fichier de préférence ?