[EXCEL2019] Erreur 2029 lors d'un Evaluate(function)

Bonjour le forum,

J'ai récemment repris le VBA pour aider quelqu'un à automatiser la génération d'une fiche.

Pour faire cela j'ai besoin de récupérer le résultat de la formule matricielle suivante :

=INDEX(Clients!$C$4:$F$35;EQUIV(1;(Clients!$A$4:$A$35 = STXT('modèle'!$C$7;1;CHERCHE(" ";'modèle'!$C$7)-1))*(Clients!$B$4:$B$35 = STXT('modèle'!$C$7;CHERCHE(" ";'modèle'!$C$7)+1;NBCAR('modèle'!$C$7)));0);1)

Afin d'en obtenir la version vba j'ai utilisé l'enregistreur de macro et j'ai obtenue la ligne suivante :

Selection.FormulaArray = _
        "=INDEX(Clients!R4C3:R35C6,MATCH(1,(Clients!R4C1:R35C1 = MID('modèle'!R7C3,1,SEARCH("" "",'modèle'!R7C3)-1))*(Clients!R4C2:R35C2 = MID('modèle'!R7C3,SEARCH("" "",'modèle'!R7C3)+1,LEN('modèle'!R7C3))),0),1)"

Mais lors de l'exécution de la macro créé j'ai l'erreur suivante :

Erreur d'exécution 1004 : Impossible de définir la propriété FormulaArray de la classe Range.

Vu que ma formule est une formule matricielle, j'ai pensé que c'était la cause de l'erreur, j'ai donc utilisé la fonction Evaluate afin d'éxécuter la fonction en matricielle :

Selection.value = _

Evaluate([ INDEX(Clients!R4C3:R35C6,MATCH(1,(Clients!R4C1:R35C1 = MID('modèle'!R7C3,1,SEARCH("" "",'modèle'!R7C3)-1))*(Clients!R4C2:R35C2 = MID('modèle'!R7C3,SEARCH("" "",'modèle'!R7C3)+1,LEN('modèle'!R7C3))),0),1) ])

Mais j'ai obtenu une erreur m'indiquant que la fonction à évaluer était trop longue. J'ai donc directement essayé de l'intégrer dans mon code, qui lui , utilise des variables, cela dois donc réduire le code à évaluer. Le code :

    'Classeur = Nom de mon classeur
    'FeuilleClients = Nom de la feuille client
    'CB_Client.Value = La valeur sélectionnée d'une combobox (colonne 1)
    'CB_Client.List(CB_Client.ListIndex, 1) = La valeur sélectionnée d'une combobox (colonne 2)

    preClient = 4
    derClient = Workbooks(Classeur).Worksheets(FeuilleClients).Cells(Rows.Count, 1).End(xlUp).Row
    plageNom = Workbooks(Classeur).Worksheets(FeuilleClients).Name & "!" & _
        Workbooks(Classeur).Worksheets(FeuilleClients).Range(Cells(preClient, 1).Address, Cells(derClient, 1).Address).Address
    plagePrenom = Workbooks(Classeur).Worksheets(FeuilleClients).Name & "!" & _
        Workbooks(Classeur).Worksheets(FeuilleClients).Range(Cells(preClient, 2).Address, Cells(derClient, 2).Address).Address
    plageInfos = Workbooks(Classeur).Worksheets(FeuilleClients).Name & "!" & _
        Workbooks(Classeur).Worksheets(FeuilleClients).Range(Cells(preClient, 3).Address, Cells(derClient, 6).Address).Address

    X = Evaluate([ INDEX(plageInfos,MATCH(1,(plageNom = CB_Client.Value)*(plagePrenom = CB_Client.List(CB_Client.ListIndex, 1)),0),1) ])

Malheureusement depuis la fenêtre espion, je remarque que la variable X est remplis avec l'erreur 2029 et si je l'inscrit directement dans une cellule j'obtiens

"#NOM?"

Auriez vous une idée de comment je pourrais résoudre cela ?

Au final le but est de sortir l'adresse d'un client depuis une table de client possédant ses informations

La table est sous cette forme :

Nom        |Prénom        |Adresse        |Code postal        |Ville        |Téléphone
xxx        |xxx           |xxx            |xxxxx              |xxx          |xxxxxxxxxx
                                        .
                                        .

Pour cela je possède les données Nom et Prénom. Ces deux données doivent être respecté car des personnes portant le même nom ou Prénom peuvent exister/existent dans le tableau.

Auriez vous sinon une autre solution à proposer ?

Merci d'avance pour vos future réponses sui, je l'espère me débloquera :)

Bonjour,

pour les matricielles en vba, je procède toujours en 2 temps pour éliminer pas mal de problèmes.
D'abord inscription en normal, puis revalidation en matricielle.
Ce qui donnerait pour toi :

Selection.Formula = "=INDEX(Clients!R4C3:R35C6,MATCH(1,(Clients!R4C1:R35C1 = MID('modèle'!R7C3,1,SEARCH("" "",'modèle'!R7C3)-1))*(Clients!R4C2:R35C2 = MID('modèle'!R7C3,SEARCH("" "",'modèle'!R7C3)+1,LEN('modèle'!R7C3))),0),1)"
Selection.FormulaArray = Selection.Formula

eric

Bonjour eriiic et merci pour ta réponse,

J'ai essayé ce que tu m'a donné et cela fonctionne plus ou moins. La fonction que je veux effectué n'a pas l'air d'aimer prendre des données sur des feuilles différentes ...
J'essaie de faire ce que tu m'a conseillé, qui fonctionne, mais je n'arrive pas vraiment à modifier ma formule pour prendre des variables

La nuit porte conseil et je suis arrivé à une autre solution, vu que je suis en vba je n'ai besoin que de la ligne concerné, je peux donc me débarasser du index, ce qui réduit ma formule.

Malheureusement je n'arrive pas à enregistrer le résultat de ma formule dans une variable, saurais-tu le faire ?

J'aimerai faire :

nom = "Arnold"
prenom = "Marcel"

X = WorksheetsFunction.Match(1, (Worksheets("Clients").Range("$A$4:$A$35") = """ + nom + """) * (Worksheets("Clients").Range("$B$4:$B$35") = """ + prenom + """), 0)

MsgBox X ' numéro de ligne ayant pour nom "Arnold" et prenom "Marcel"

mais j'ai une incompatibilité de type, surement du au fait que cela doit être une formule matricielle

Bonjour,

sûrement qu'avec un fichier de travail et des explications complètes on y arrivera sans tourner en rond comme actuellement.
eric

Bonjour,

désolé du délais de réponse, je ne m'y était pas remis depuis.

Apres quelques recherches j'ai découvert qu'il valais mieux faire une boucle pour mon cas, les formules étant plus destiné à être insérer dans une feuille qu'a être enregistre dans des variables.

Je te remercie encore pour le temps que tu as pris pour me répondre.