Microsoft 365 Problème de référence à une Feuil de calcul avec .FormulaLocal

[spike29]

Bonsoir le Forum,

Je rencontre un problème avec l'utilisation de la formule .FormulaLocal.
En effet, j'intègre dans l'un de mes code VBA des .FormulaLocal (voir ci-dessous) :

[G8:G15000].FormulaLocal = "=SIERREUR(INDEX(TEST!$B$5:$B$14985;EQUIV(1;(TEST!$B$5:$B$14985=Feuil1!$B8)*(TEST!$D$5:$D$14985=Feuil1!$C8);0);1);0)"
[H8:H15000].FormulaLocal = "=SIERREUR(INDEX(TEST!$C$5:$C$14985;EQUIV(1;(TEST!$B$5:$B$14985=Feuil1!$B7)*(TEST!$D$5:$D$14985=Feuil1!$C7);0);1);0)"

Hors VBA, le code fonctionne sans problème. En l'utilisant avec la formule FormulaLocal j'ai des @ qui apparaissent devant le nom de la Feuil TEST ce qui donne ça :
=SIERREUR(INDEX(TEST!$B$5:$B$14985;EQUIV(1;(@TEST!$B$5:$B$14985=Feuil1!$B10)*(@TEST!$D$5:$D$14985=Feuil1!$C10);0);1);0)

J'imagine qu'il s'agit d'une erreur de syntaxe mais je n'arrive pas à l'identifier. J'ai essayé d'ajouter des ' avant, puis avant et après le nom de la feuil mais toujours ce problème.

Autre question, dans la Feuil1 de mon fichier j'ai la colonne H ou apparaissent des dates via mon second Formula.Local. J'aimerai via un code VBA convertir ces dates en jour et cela pour l'ensemble des cellules non vide en partant de H7.
Hors VBA cela se fait très bien avec la formule "CHOISIR(JOURSEM" mais je tiens à utiliser du VBA pour cela.
J'ai trouvé le code Weekday mais je n'arrive pas à l'intégrer proprement dans une boucle.


En PJ, un fichier illustrant mes problématiques.

Bonne fin de journée et merci d'avance.

 

[sylvanu]

Bonjour Spike,

J'imagine qu'il s'agit d'une erreur de syntaxe

Non, ces @ sont rajouté par 365.
L'erreur vient du fait que vos formules sont matricielles. On ne peut les mettre avec FormulaLocal, mais avec FormulaArray.
Mais les formules doivent être en anglais.
Un essai en PJ avec :

Sub Copie()
[G7:H15000].ClearContents
Application.ScreenUpdating = False
Formule1 = "=IFERROR(INDEX(TEST!$B$5:$B$14985,MATCH(1,(TEST!$B$5:$B$14985=Feuil1!$B7)*(TEST!$D$5:$D$14985=Feuil1!$C7),0),1),0)"
Formule2 = "=IFERROR(1*INDEX(TEST!$C$5:$C$14985,MATCH(1,(TEST!$B$5:$B$14985=Feuil1!$B7)*(TEST!$D$5:$D$14985=Feuil1!$C7),0),1),0)"
With Sheets("Feuil1")
    [G7].FormulaArray = Formule1
    [H7].FormulaArray = Formule2
    [G7:H7].AutoFill Destination:=[G7:H15000], Type:=xlFillDefault
    [H7:H15000].NumberFormat = "dddd"
End With
End Sub

Le second problème est que la date est inscrite en texte et non en nombre, d'où l'ajout du "1*" devant l'Index pour le transformer en nombre.
Ceci dit sur une plage de 15000 lignes c'est extrêmement, extrêmement long. 🙂

 

[sylvanu]

Re,
Si vous avez la possibilité de rajouter une colonne dans la feuille TEST colonne H avec : =B5&E5 pour la ligne 5, alors la formule de recherche devient beaucoup plus simple :

NUM    :  =SIERREUR(INDEX(TEST!B:B;EQUIV(Feuil1!B7&Feuil1!C7;TEST!H:H;0));"")
Date J :  =SIERREUR(INDEX(TEST!D:D;EQUIV(Feuil1!B7&Feuil1!C7;TEST!H:H;0));"")

Les formules sont non matricielles et la macro serait beaucoup plus rapide, et vous pouvez utiliser directement FormulaLocal.

 

[Dranreb]

Bonjour.
On arriverait aussi surement aussi à faire beaucoup plus rapide avec une fonction perso en VBA.

 

[sylvanu]

Bonjour Dranreb,
Mais ça devrait tout de même 30000 fonctions perso à exécuter. 🙂
Autant passer par un module. Avec 3 arrays c''est plus rapide.
Avec la macro de cette PJ c'est immédiat, en grande partie parce que on s'arrête à la fin du tableau et non pas en ligne 15000.

 

[spike29]

Bonjour Dranreb,
Mais ça devrait tout de même 30000 fonctions perso à exécuter. 🙂
Autant passer par un module. Avec 3 arrays c''est plus rapide.
Avec la macro de cette PJ c'est immédiat, en grande partie parce que on s'arrête à la fin du tableau et non pas en ligne 15000.

Bonsoir à tous et désolé de la réponse tardive.

Merci pour vos réponses.
@sylvanu ton dernier message tombe à point, j'allais justement réfléchir à mettre en place une ou plusieurs boucles pour ne plus avoir à utiliser FormulaLocal.
Mes tableaux faisant en moyenne 6000 lignes ta dernières macro s'exécute en moins de 5 sec ce qui au vu du nombre de ligne est plus que correct.

Toutefois, je suis resté sur ta 2ème proposition code suivant :

Sub Copie()
[G7:H15000].ClearContents
Application.ScreenUpdating = False
Formule1 = "=SIERREUR(INDEX(TEST!B:B;EQUIV(B7&C7;TEST!H:H;0));0)"
Formule2 = "=SIERREUR(INDEX(TEST!D:D;EQUIV(B7&C7;TEST!H:H;0));0)"
With Sheets("Feuil1")
    [G7:G15000].FormulaLocal = Formule1
    [H7:H15000].FormulaLocal = Formule2
    [G7:H15000].NumberFormat = "General;;"
End With
End Sub

Ici le calcul prend moins d'une seconde. En PJ le fichier avec les deux codes de @sylvanu.

Encore merci et bonne fin de journée 🙂

 

[sylvanu]

Re,
On peut encore un peu accélérer en ne copiant les formules que sur l'étendu du tableau ( en PJ 5599 ) au lieu de 15000 écrit en "dur"
Sur mon XL2007 j'ai un gain sur le tableau de la PJ de 25%. ( par contre si ce nombre est de 15000 lignes on ne gagne rien. 🙂 )