XL 2019 formule index equiv en VBA

[Loïc DUBOIS]

Bonjour à tous,

J'espère que vous allez bien ?

Pour retrouver des données dans un tableau j'utilise la formule index equiv : =INDEX(Feuil5!$C$2:$BBA$20000;EQUIV(Feuil1!$D223232&Feuil1!AF$1;Feuil5!$A$2:$A$20000&Feuil5!$B$2:$B$20000;0);EQUIV(Feuil1!$Z223232*1;Feuil5!$C$1:$BBA$1;0))

Comme vous pouvez le voir il y a deux critères de colonne et un critère de ligne. Cette formule fonctionne très bien. Le problème étant que j'ai de gros fichiers de données actuellement (600k lignes) et le traitement est très long. En sachant que j'ai environ 32 tableaux de résultats le traitement prend un temps infini.

Dernière précision la colonne A représente le groupe (en gros je vais faire la requête groupe par groupe (ils sont possiblement dans le désordre) donc j'aimerais aussi qu'on prenne en compte cela).

En gros, on applique le code pour le groupe 2 (pour les autres groupes je veux laisser vide (en effet, je vais appliquer le code dans un second temps (donc il ne faut pas que le code change les groupes déjà fait).

Avez-vous une solution pour le faire en code VBA svp ?

Je vous joins un fichier test

Merci d'avance,

 

[sylvanu]

Bonjour Loïc,
Si les calculs sont tout fait en VBA cela d'être plug lent qu'avec XL.
Par contre les formules sont recalculées à chaque nouveau calcul, d'où la lenteur.
Peut être un compromis :
1- En VBA, on met les formules dans la plage désirée
2- On copie colle les valeurs
Je pense que ce sera l'optimum en terme de temps.
On profite ainsi de la vitesse de calcul d'XL, mais il n'y a aucun recalcul posterieur.

Un ex en PJ pour la plage Feuil1 en rose :

Sub FormuleFeuil1()
    ' Formule peut être acquise directement par l'enregistreur de macro
    Formule = "=IFERROR(INDEX(Feuil5!R2C3:R31C449,MATCH(Feuil1!RC4&Feuil1!R1C,Feuil5!R2C1:R31C1&Feuil5!R2C2:R31C2,0),MATCH(Feuil1!RC26*1,Feuil5!R1C3:R1C449,0)),"""")"
    Sheets("Feuil1").[AF2].FormulaArray = Formule   ' On colle la formule
    Range("AF2").Copy                               ' On duplique cette formule sur la plage
    Range("AF2").AutoFill Destination:=Range("AF2:AF23"), Type:=xlFillDefault
    Range("AF2:AF23").AutoFill Destination:=Range("AF2:AL23"), Type:=xlFillDefault
    Range("AF2:AL23") = Range("AF2:AL23").Value     ' On colle les valeurs
    Range("AF2").Select
    Application.CutCopyMode = False
End Sub

C'est brut de fonderie, sans optimisation. Juste pour valider le principe.

 

[Loïc DUBOIS]

Merci beaucoup pour ta proposition.

Sur le fichier test cela foncitonne très bien.
En revanche sur mon fichier, j'aimerais savoir ce qu'il faut que je modifie pour la taille des tableaux. En effet, ma feuil1 contient + de 250k lignes.

De plus sur la formule :

=IFERROR(INDEX(Feuil5!R2C3:R31C449,MATCH(Feuil1!RC4&Feuil1!R1C,Feuil5!R2C1:R31C1&Feuil5!R2C2:R31C2,0),MATCH(Feuil1!RC26*1,Feuil5!R1C3:R1C449,0)),"""")"

A quoi correspond les R2C3, R31C449 etc ? j'avoue ne pas comprendre la notation.

Merci d'avance,

Loïc

 

[Loïc DUBOIS]

Un autre problème que je viens de voir. En faisant cette méthode, le problème est que j'ai 8 tableaux différents donc je vais faire le groupe 1 (cela va me faire 30k lignes sur les 250k) puis le groupe 2 etc.. mais en collant la formule cela va me remplacer à chaque fois le groupe précédent. Donc les valeurs retournées sur le groupe 1 par exemple vont etre écrasées. J'aimerais qu'on puisse appliquer la formule uniquement pour un groupe défini (voir colone A).

Vois-tu ce que je veux dire ?

Merci d'avance,

Loïc

 

[sylvanu]

Re,
1- Pour localiser une cellule XL permet de le faire soit avec une notation A2 ou une notation L2C1.
(Ligne 2 Colonne 1)
Mais il vous suffit de mettre la bonne formule dans une cellule, d'enregistrer une macro, de vous repositionner dans la cellule puis de faire Entrée. Vous arrêtez l'enregistreur et dans la macro vous avez la formule. ( il vous faut supprimer les { et } de cette formule.

2- Il vous faut écrire une macro pour chaque type de formule avec la bonne plage sur laquelle elle doit s'appliquer, exactement comme vous le feriez à la main, puisque le VBA ne va faire que coller les formules et faire un copier coller valeurs.

 

[Loïc DUBOIS]

Re,

Merci encore pour ta réponse,

JE viens d'essayer sur mon gros fichier :

Sub FormuleFeuil1()
' Formule peut être acquise directement par l'enregistreur de macro
Formule = "=IFERROR(INDEX(Feuil5!R2C3:R20000C449,MATCH(Feuil1!RC5&Feuil1!R1C,Feuil5!R2C1:R20000C1&Feuil5!R2C2:R20000C2,0),MATCH(Feuil1!RC25*1,Feuil5!R1C3:R1C449,0)),"""")"
Sheets("Feuil1").[AD2].FormulaArray = Formule ' On colle la formule
Range("AD2").Copy ' On duplique cette formule sur la plage
Range("AD2").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault
Range("AD2:AH31200").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault
Range("AD2:AH31200") = Range("AD2:AH31200").Value ' On colle les valeurs
Range("AD2").Select
Application.CutCopyMode = False
End Sub


Mon problème est que j'ai un message d'erreur :

Pourtant je n'ai juste modifié que la formule pour qu'elle soit adapté à mon fichier et les ranges...

As-tu une idée ?

Merci

 

[Loïc DUBOIS]

Désolé pour les mulitples postes mais je n'ai pu cette erreur.

J'en ai une autre, le fait de copier coller en valeur de fonctionne pas.
La macro n'exécute que cela :
Sub FormuleFeuil1()
' Formule peut être acquise directement par l'enregistreur de macro
Formule = "=IFERROR(INDEX(Feuil5!R2C3:R20000C449,MATCH(Feuil1!RC5&Feuil1!R1C,Feuil5!R2C1:R20000C1&Feuil5!R2C2:R20000C2,0),MATCH(Feuil1!RC25*1,Feuil5!R1C3:R1C449,0)),"""")"
Sheets("Feuil1").[AD2].FormulaArray = Formule ' On colle la formule
Range("AD2").Copy ' On duplique cette formule sur la plage
Range("AD2").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault

De plus le script ne colle pas la formule sur la colonne AD...

 

[sylvanu]

Avec FormulaArray, on ne peut pas dupliquer en même temps sur lignes et colonnes.
On duplique déjà sur les lignes puis la colonnes sur les autres.
Regardez bien sur mon ex. Dupliquez un FormulaArraysur une matrice ne marche pas.
Utilisez plutôt :

Sub FormuleFeuil1()
' Formule peut être acquise directement par l'enregistreur de macro
Formule = "=IFERROR(INDEX(Feuil5!R2C3:R20000C449,MATCH(Feuil1!RC5&Feuil1!R1C,Feuil5!R2C1:R20000C1&Feuil5!R2C2:R20000C2,0),MATCH(Feuil1!RC25*1,Feuil5!R1C3:R1C449,0)),"""")"
Sheets("Feuil1").[AD2].FormulaArray = Formule ' On colle la formule
Range("AD2").Copy ' On duplique cette formule sur la plage
' On duplique cette formule sur la colonne AD
Range("AD2").AutoFill Destination:=Range("AD2:AD31200"), Type:=xlFillDefault
' On duplique cette colonne sur l'ensemble des colonnes
Range("AD2:AD31200").AutoFill Destination:=Range("AD2:AH31200"), Type:=xlFillDefault
Range("AD2:AH31200") = Range("AD2:AH31200").Value ' On colle les valeurs
Range("AD2").Select
Application.CutCopyMode = False
End Sub

 

[Loïc DUBOIS]

Super merci beaucoup Sylvanu, ca fonctionne !

 

[sylvanu]

Mais ça rame !

 

[Loïc DUBOIS]

Un peu oui, mais ca prend déjà beaucoup moins de temps que sans passer par ce script.

Donc merci beaucoup