recherchev ou index indirect equiv

[st007]

Bonsoir,

Pour mon soucis de rapidité d'une macro, quel formule est plus judicieuse ?

=RECHERCHEV(GAUCHE($B$2;13);I9000001!A2:E250;5;FAUX)

ou bien

=INDEX(INDIRECT(A5&"!$E$2:$E$250");EQUIV(GAUCHE($B$2;13);INDIRECT(A5&"!$A$2:$A$250");1))

formules utilisées 5000 fois sur une feuille

 

[job75]

Re : recherchev ou index indirect equiv

Bonsoir st007,

La formule la meilleure est la plus rapide.

La macro que j'ai créée sur ce fil déterminera la durée d'exécution de chaque formule :

https://www.excel-downloads.com/threads/duree-dexecution-dune-formule.148727/

Bonne nuit et A+

 

[st007]

Re : recherchev ou index indirect equiv

Merci job75

A priori donc la seconde donc je vai essayer de transformer

"=VLOOKUP(" & Chr(34) & Left(.Cells(2, i + 1).Value, 13) & Chr(34) & "," & no & "!A:F,5,False)"

en
=INDEX(INDIRECT(A5&"!$E$2:$E$250");EQUIV(GAUCHE($B$2;13);INDIRECT(A5&"!$A$2:$A$250");1))

" & no & " me donne le nom de l'onglet (A5)
Left(.Cells(2, i + 1).Value, 13) equivaut à GAUCHE($B$2;13)
peux-tu m'aider, après d'innombrables essais, çà beugue toujours, et je ne comprends pas &chr(34)

dest.Offset(0, i).Formula = "=INDEX(INDIRECT(" & no & "!$E$2:$E$250""),MATCH(LEFT(.Cells(2, i + 1).Value, 13),INDIRECT(" & no & "!$A$2:$A$250""),0))"

qu'est-ce qui coince ?

 

[Misange]

Re : recherchev ou index indirect equiv

Bonjour

pas besoin d'indirect avec VBA. Reconstruis ta référence en VBA :

Sub test()
Dim mafeuille As String
mafeuille = Sheets("A").Range("A5").Value

With Sheets(mafeuille)
truc = Application.WorksheetFunction.Index(Range("E2:E250"), Application.WorksheetFunction.Match(Left(Sheets("A").Range("B2"), 13), .Range("A2:A250"), 1))
End With
Sheets("A").Range("A2") = truc

End Sub

 

[job75]

Re : recherchev ou index indirect equiv

Bonjour st007, Misange,

Si je comprends vous voulez utiliser la valeur de A5 "en dur" dans la formule.

Alors ceci fonctionnera bien s'il y a une feuille nommée "I9000001" :

Set dest = [A1]
i = 1
no = "I9000001" '[A5].Value
dest.Offset(0, i).Formula = "=INDEX(INDIRECT(""" & no & "!$E$2:$E$250""),MATCH(LEFT(""" & Cells(2, i + 1).Value & """, 13),INDIRECT(""" & no & "!$A$2:$A$250""),0))"

INDIRECT n'est pas forcément indispensable, ceci fonctionnera bien aussi :

Set dest = [A1]
i = 1
no = "I9000001" '[A5].Value
dest.Offset(0, i).Formula = "=INDEX(" & no & "!$E$2:$E$250,MATCH(LEFT(""" & Cells(2, i + 1).Value & """, 13)," & no & "!$A$2:$A$250),0)"

Noter que j'ai enlevé le point devant Cells pour tester sur la feuille active.

A+

 

[job75]

Re : recherchev ou index indirect equiv

Re,

Sur Win7 - Excel 2010, durées d'exécution des formules :

- RECHERCHEV... => 28 µs

- INDEX(INDIRECT... => 39 µs

- INDEX(... sans INDIRECT => 33 µs.

A+

 

[st007]

Re : recherchev ou index indirect equiv

re bonjour,misange,job75,le forum

Merci Job75
çà fonctionne bien sure, j'ai du remettre le "."devant Cells. Et 3 guillemets, j'y étais pas arrivé je me battais avec Chr(34)
le comble de l'histoire, j'ai a peine gagné 2 secondes grrrr...

édit, j'avais copier mes formules pour les essayer dans durée d’exécution d'une formule et index... était plus rapide

 

[job75]

Re : recherchev ou index indirect equiv

Re,

formules utilisées 5000 fois sur une feuille

Si l'on entre la formule 5000 fois c'est forcément long.

Si on la rentre d'un seul coup dans une plage c'est rapide.

Utiliser alors la notation R1C1 avec FormulaR1C1 et (peut-être) des références relatives dans la formule.

Mais sans fichier je ne sais pas ce que vous voulez faire et je ne peux donc vous aider plus.

A+

 

[st007]

Re : recherchev ou index indirect equiv

Re,

le fil de départ si je puis dire est :
https://www.excel-downloads.com/threads/macro-import-fichier-txt-nouveau-onglet-et-recap.203491/

et comme je rame encore sur la création en vba, je me suis bien accomodé de la macro de Robert et de quelques morceaux et idées piochées parci parlà.
J'avais quelques fichiers à traiter par mois, mais l'évolution fait que j'ai le même nombre mais par semaine, et du coup le délai d'exécution devient gênant.

 

[Misange]

Re : recherchev ou index indirect equiv

Re bonjour,
Une macro n'est pas plus rapide qu'une formule, bien au contraire. Ca ne sert strictement à rien d'écrire par macro une formule dans une feuille. Le VBA est en effet beaucoup moins rapide que le langage de programmation interne utilisé pour les formules.
Ce qui est intéressant c'est de mettre le résultat d'une formule par macro, et ceci uniquement dans le but d'éviter que l'ensemble des cellules d'une feuille ou d'une plage par exemple soit recalculé dès qu'on modifie un truc sur cette feuille.