Formule matricielle en VBA

Bonjour,

Je tente désespérément de trouver une solution à mon problème.

J'utilise une formule matricielle pour remplir un tableau. La formule a pour objectif de filtrer et classer des informations issues d'une base de données.

{=SI(LIGNES($1:1)<=NB.SI(Nom_site;F$1);INDEX(Nom_salle;PETITE.VALEUR(SI(ESTNUM(CHERCHE(F$1;Nom_site));LIGNE(INDIRECT("1:"&LIGNES(Nom_salle))));LIGNES($1:1)));"")}

J'ai besoin pour l'utilisation de listes déroulantes en cascades d'avoir des valeurs dans ces cellules plutôt que les formules d'où le fait de traduire cette formule en code.

Jusqu'à présent, d'après quelques recherches sur les forums (celui-ci en fait partie), je n'ai que ça mais ça ne fonctionne pas.

Private Sub Worksheet_Activate()
'Actualise les formules de la Feuil3 afin de prendre en compte les ajouts ou suppression de lignes dans le tableau de la feuille 'Liste'

    'Dim i As Long
    'ActiveWorkbook.Sheets("Tables").[A2:C400].Clear
    'ActiveWorkbook.Sheets("Tables").[E2:BA2].Clear

    'For i = 2 To 400

    'ActiveWorkbook.Sheets("Tables").Cells(i, 1).FormulaR1C1 = ActiveWorkbook.Sheets("Liste").Cells(i, 1).Value & " - " & ActiveWorkbook.Sheets("Liste").Cells(i, 2).Value
    'Next i
    'ActiveWorkbook.Sheets("Liste").Range("A2:A400").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets("Tables").Range("C2"), Unique:=True
    'Range("C2:C400").Copy
    'Range("E2").PasteSpecial Paste:=xlPasteAll, Transpose:=True

    Dim cell As Range
    'Dim j As Integer
    'Dim k As Integer
    For Each cell In Range("F2:BA71")

    'For j = 2 To 71
    'For k = 6 To 80
    Dim x
    x = Evaluate([if(rowS($1:1)<=countif(Nom_site,F$1),INDEX(Nom_salle,small(if(isnumberM(search(F$1,Nom_site)),row(INDIRECT("1,"&rowS(Nom_salle)))),rowS($1:1))),"")])
    cell = x

    Next cell
    'Set cell = cell.Offset(1, 0)
    'Next j
    'Next k

End Sub

Je teste le bout de code final sans résultat. Quelqu'un aurait-il une solution à mon problème ?

Merci

Bonsoir,

si vous regardez pas mal de forum, en particulier celui-ci vous avez certainement vu qu'avec un fichier joint les réponses sont plus rapides et plus pertinantes, non ?

En effet aider c'est bien, se "taper" la totalité d'un fichier pour répondre c'est "lourd"...

Même si c'est un simple support simplifié par rapport à l'original, avec une partie des codes déjà tapés etc... Je vous assure que ça va plus vite ! d'ailleurs c'est écrit dans les conseilles et recommandations d'utilisation du forum...

@ bientôt

LouReeD

Bonjour,

J'appuie compètement les propos de LouReed !

Lorsque j'ai vu ton sujet il y a quelques jours, ma première réaction est que le vrai problème est dans la constitution de listes-cascade (que tu ne sembles pas maîtriser) et que tu devrais donc poser clairement avec fichier à l'appui...

Cordialement

bonjour à tous

une suggestion sans avoir vu le fichier :

• copier la zone contenant les formules
• la "coller spécial valeurs"

- se servir de cette zone collée pour alimenter les listes déroulantes

Vi, voici un fichier du même style.

Bonjour,

Je te retourne ton fichier avec une illustration de gestion de listes-cascades, simple puisque 2 listes seulement.

Le principe (sur la feuille Tables : une liste sur 2 colonnes Site et Salle qui constitue la Base (nommée Base en plage dynamique). C'est la seule sur laquelle on intervient, en ajoutant des données Site-Salle. Le reste se fait automatiquement.

[NB: j'ai dû virer ta proc Activate, hélas après qu'elle ait détruit ce que j'avais commencé et ai dû refaire... Mais cela indique que ta mise à jour s'effectue en fait sur la feuille Liste. Pour conserver ce dispositif, il conviendrait de mettre en place une proc. Change pour Liste qui mettrait à jour la Base sur Tables...]

Lorsqu'un élément Site-Salle est ajouté à la Base sur Tables (ou modifié), Base est retriée sur les 2 colonnes par une proc. Change. La proc. procède ensuite à un filtrage de la 1re colonne (nommée SiteSalle) pour constituer la liste Site (nommée, également en champ dynamique).

Site alimente la première liste déroulante. La seconde, dépendante du 1er choix est définie par une formule qui décale SiteSalle pour ne lister que les salles correspondant au site choisi (voir Validation...)

La recherche des autres éléments dans Liste m'a paru adéquate, et pour les équipements les éléments manquent.

A toi de d'analyser le système et poursuivre la réflexion.

Cordialement

[Et un salut à Mytå par qui j'ai obtenu le modèle de Listes cascade utilisé...]

Merci.

La feuille 'Tables' ne se remplit pas toute seule à partir des données de la feuille 'Liste', d'où le Activate.Un changement sur la feuille 'Liste' n'engendre pas de mise à jour des données sur la feuille 'Tables'.

De plus, le transfert de l'onglet sur le fichier sur lequel je travaille ne fonctionne pas, notamment sur les listes déroulantes. Je pense qu'un tri sur les noms gérés est nécessaire, mais il doit y avoir un autre problème.

Je fouille et reviens.

La feuille 'Tables' ne se remplit pas toute seule à partir des données de la feuille 'Liste', d'où le Activate.Un changement sur la feuille 'Liste' n'engendre pas de mise à jour des données sur la feuille 'Tables'.

C'est bien ce que j'ai dit ! Qu'en l'état actuel pour avoir une mise à jour automatique de Tables à partir de ta feuille Liste, il convient de programmer une procédure Change pour Liste, qui lors de la saisie d'un nouveau couple Site-Salle sur Liste, le transfère sur Tables (où la procédure Change de Tables prend le relais pour mettre à jour les tables).

Selon le cas et la nature des mises à jour, il sera d'ailleurs peut-être plus opportun lors de modifications de substituer la Base complète de Tables à partir de Liste.

Mais pour que cela se fasse, il faut d'abord écrire la procédure !

Un nettoyage des noms gérés a permis de régler le problème.

Pour l'appel d'une procédure, je tente avec ça, mais forcément, ça ne marche pas.

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim monRange As Range
        Set monRange = Sheets("Feuil13").Range("A2")
        Call Worksheet_Change(monRange)

End Sub

Code inséré sur la feuille 'Liste'.

Je ne comprends pas ce que tu veux faire !

Pour que les données soient sur Tables afin de pouvoir y être traitées, elles doivent être transférées de Liste sur Tables par une procédure Change sur Liste.

Soit après vérification que des changements affectent A et B sur Liste, prélèvement de la plage sur Liste, effacement de la Base sur Tables et remplacement par la plage prélevée.

Le cas échéant, si tu fais des modifications groupées, il serait peut-être judicieux de ne pas utiliser l'évènement et de lancer la mise à jour de Tables par un bouton appelant une procédure normale...

Dans le cas où l'on procèderait par transfert de l'ensemble de la Base, il faudra que je revoie la procédure de Tables qui prévoie des mises à jour par élément (couple Site-Salle) pour l'adapter...

Cordialement

L'onglet 'Tables' n'est censé être qu'une feuille pivot pour 'alléger' les quelques formules qui resteront. A terme, celui-ci sera masqué donc pas de changement par l'utilisateur. Ce dernier se servira uniquement de l'onglet liste pour ajouter ou mettre à jour des données/sites/salles.

Les onglets qui subiront le plus de changements seront ceux dédiés aux sites eux-mêmes par la réalisation d'audits, par exemple.

Tu ne lis pas ce que j'écris, faut croire !

Jamais parlé de bouton sur Tables !!!

Qu'elle soit masquée importe peu, mais c'est généralement préférable puisqu'on n'a rien à y faire.

Ai-je parlé de boutons ? Simplement, actuellement, la mise à jour ne se fait QUE si on modifie un élément de la feuille.

Une solution intermédiaire, qui conserve l'automatisme (donc se passe de bouton).

Procédure Change de la feuille Liste qui affecte la valeur True à une variable booléenne (niveau Module) lorsque changement dans les colonnes A et B.

A la désactivation de la feuille, cette variable est testée (proc. Deactivate) et la mise à jour de Tables est effectuée s'il y a lieu.

Petit complément (omis précédemment) sur Recherche, pour que C2 soit effacée lors de changement de B2 (sinon une salle d'un autre site, précédemment sélectionnée pourrait demeurer...)

Cordialement

Merci. Mais la macro semble bogguer, que ce soit dans le fichier que tu as envoyé (j'ai ajouté une ligne en tirant la dernière ligne pour créer une nouvelle entrée) où une fois la macro copiée-collée dans mon fichier destination. Et le bug n'est pas au même endroit, bizarrement entre les deux fichiers. Sinon, l'idée est bien là : c'est clairement ce qui me conviendrait.

Une macro, c'est précis. Si tu modifies son environnement d'exécution sans en évaluer au préalable les répercussions, il ne faut pas t'étonner que cela bogue... !

redblood a écrit :

... la macro semble bogguer, que ce soit dans le fichier que tu as envoyé (j'ai ajouté une ligne en tirant la dernière ligne pour créer une nouvelle entrée) ...

Ne me dis pas que ça va jusqu'à l'ordinateur sur lequel le fichier est ouvert.

Sinon, à la lecture, seules les colonnes A et B de la feuille 'Listes' semblent concernées, c'est bien ça ? Il me faut donc modifier les lignes en conséquence pour étendre sur tout le tableau de la feuille en question.

Excuses d'abord pour ma réaction inadaptée à ton sujet : j'ai cru répondre sur un autre sujet en cours !

Au temps pour moi.

Ceci étant, je ne vois pas trop ce que signifie dans ton fichier "tirer une ligne pour créer une nouvelle entrée" ?

Il n'y a pas d'entrée à créer, les plages sont nommées dynamiquement.

Et Tables ne peut être concernée que par les colonnes A et B de Liste.

Et ça boguerait où : quelle ligne ? quel code erreur ? après quelle manipulation ?

Si ton modèle est conforme à la configuration de ton fichier réel, pas de raison que cela bogue.

Sur le fichier d'origine (celui que tu m'as transmis après transformation), le code erreur est 424. Il s'arrête sur la macro de la feuille 'Liste' à la ligne

[Base].ClearContents

Si je prends le code et que je l'insère dans mon fichier (même feuille de code), j'ai une erreur 1004 'Le nom de champ est incorrect ou manquant dans la zone d'extraction'. Il s'arrête sur le code de la feuille 'Tables ' à la ligne

[SiteSalle].AdvancedFilter Action:=xlFilterCopy, copytorange:=[E2], _
                 unique:=True

ll met à jour les deux colonnes de la feuille 'Tables', mais pas la liste de la colonne E (celle triée).

J'ai contrôlé les noms gérés dans la liste pour voir si j'avais les mêmes références et c'est bien le cas. Hormis la quantité et la nature des données, les fichiers sont identiques.

Sur le fichier d'origine (celui que tu m'as transmis après transformation), le code erreur est 424. Il s'arrête sur la macro de la feuille 'Liste' à la ligne

[Base].ClearContents

Si je prends le code et que je l'insère dans mon fichier (même feuille de code), j'ai une erreur 1004 'Le nom de champ est incorrect ou manquant dans la zone d'extraction'. Il s'arrête sur le code de la feuille 'Tables ' à la ligne

[SiteSalle].AdvancedFilter Action:=xlFilterCopy, copytorange:=[E2], _
                 unique:=True

ll met à jour les deux colonnes de la feuille 'Tables', mais pas la liste de la colonne E (celle triée).

J'ai contrôlé les noms gérés dans la liste pour voir si j'avais les mêmes références et c'est bien le cas. Hormis la quantité et la nature des données, les fichiers sont identiques.