Validation matricielle et #N/A

Le 05/11/2012 à 16:50

Bonjour à toutes et à tous,

Désolé si le sujet à déjà été abordé mais je ne l'ai pas trouvé.

Mon code VBA de validation matricielle fonctionne mais renvoie la valeur #N/A dans les cellules de ma feuille quand il ne trouve pas de résultat.

J'ai mis cette macro pour enlever les #N/A, mais elle n'enlève que ceux qui sont propre aux formules d'Excel et non celles issue du résultat du Code

Sub a()
Dim s As Worksheet
For Each s In Worksheets
On Error Resume Next
s.UsedRange.SpecialCells(xlCellTypeFormulas, 16) = Empty
Next s
End Sub

Un exemple de mon code :

Range("E5") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D5),0))")
Range("E6") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D6),0))")
Range("E7") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D7),0))")
Range("E8") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D8),0))")
Range("E9") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D9),0))")
Range("E10") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D10),0))")
Range("E11") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D11),0))")
Range("E12") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D12),0))")

Merci pour votre aide.

D'ailleurs si vous avez une boucle à me proposer je suis preneur car j'en ai des tonnes à faire

Bagi !

Le 05/11/2012 à 17:12

Bonjour

A la place de xlCellTypeFormulas mets xlCellTypeConstants

Sub a()
Dim s As Worksheet
For Each s In Worksheets
On Error Resume Next
s.UsedRange.SpecialCells(xlCellTypeConstants, 16) = Empty
Next s
End Sub

EDIT

Pour la 2ème question si les lignes se suivent

Range("E5:E12") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D5),0))")

Le 05/11/2012 à 18:19

Banzaï 64,

Je te remercie pour ta réponse rapide.

1. Le remplacement de xlCellTypeFormulas par xlCellTypeConstants dans la macro qui supprime les #/NA fonctionne.

2. En suivant le raisonnement sous EDIT j'ai écrit pour les cases allant de E5 à E35 :

Range("E5:E35") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D5),0))")

a. cela ne me donne plus mes résultats

b. pour contrôler j'ai enlever le code supprimant les #N/A et j'ai des #N/A dans toutes les cases, preuve que la modification fonctionne pour les cellules de E5 à E35, mais sans résultats....

c. une dernière question si possible : le résultat renvoyé dans les cellules Excel par le code de la validation matricielle est une valeur entre guillemet et ne permet pas d'être reconnu pour les calculs, même en changeant le format. Que dois-je faire ?

Bagi !

Le 05/11/2012 à 20:36

Bazaï64,

Tout fonctionne. Mais je n'ai pas trouver de solution pour n'écrire qu'une ligne ou qu'une boucle car la cellule D en fin de code évolue de ligne en ligne.

Pour ce qui est du résultat au mauvais format. Le format était faux sur la feuille de départ et non dans la formule ni dans la cellule de destination.

Dans tous les cas un GRAND MERCI et problème initial résolu click !

Bonne soirée

Bagi !

Le 05/11/2012 à 20:55

Bonsoir

Moi je n'aime pas les matricielles (en plus elles me le rendent bien)

Mais une copie de ton fichier aiderait beaucoup les personnes susceptibles de t'aider et de ce fait augmenterait tes chances de trouver une solution à ton problème

Le 06/11/2012 à 08:06

A toutes, a tous

Banzaï64,

Selon tes conseils je mets en copie mes 2 fichiers :

1. Journal dynamique

2. Historique

Le principe est simple. Le journal dynamique interroge l'historique pour remplir les cellules de la feuille Janvier. Ne m'y connaissant pas en VBA, j'arrive à mes fins (du tout début lol !) mais le code est plein de lourdeurs. La suite sera la réalisation de statistique par interrogation sous forme de Userforme et contrôle. Un module d'impression pdf, et si j'arrive jusque là, au final une interface sympa.

La finalité est une analyse comportementale passée pour avertir l'utilisateur final des erreurs qu'il a souvent commises (analysé sur l'historique) et de l'orienter vers des suggestions de modification de son comportement. Le tout en temps réel puisque l'appli communique via DDE avec un programme de prise de décision.

Le code se trouve uniquement dans Journal dynamique et est détaillé pas à pas par des explications

Mes souhaits globaux et spécifiques :

1. Une mini analyse des lourdeurs

2. Réaliser les phrases suivantes en une seule phrase de code selon l'idée que tu m'as donné hier

Range("E5") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D5),0))")
Range("E6") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D6),0))")
Range("E7") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D7),0))")
par une phrase du type :
Range("E5:E35") = Evaluate("INDEX([MonHistoriqueAT.xls]MonHistoriqueAT!profit,match(1,([MonHistoriqueAT.xls]MonHistoriqueAT!deposit=""Deposit"")*([MonHistoriqueAT.xls]MonHistoriqueAT!open_time=D5),0))")
Sachant que l'adresse en fin d'instruction va aussi jusqu'à D35

3. Je n'arrive pas à convertir La cellule N4401du fichier Historique en format nombre sachant que l'adresse va changer chaque jour, mais j'arrive à me positionner dessus via l'instruction à l'Etape 2 "modifie le déposit de base Ne Fonctionne pas"

4. J'aimerais que la feuille Janvier soit en fait une feuille mensuelle et qu'elle se modifie en fonction du mois interrogé afin d'en alléger le code et pourquoi pas avoir aussi une feuille par semaine et une par jour. Ce qui aurait le mérite d'être pertinent.

En te remerciant Banzaï64,

En vous remerciant de votre aide future, j'imagine que j'en demande beaucoup trop mais sachez que le sujet intéresse de très nombreuses communautés.

Bien à vous,

Bagi !

8bagi.rar (205.38 Ko)

Rechercher des sujets similaires à "validation matricielle"