XL 2019 Optimiser un code VBA dans un grand jeu de données

Loïc DUBOIS · 7 Octobre 2023

Bonjour à tous,

J'espère que vous allez bien ?

J'ai un problème lié à un grand jeu de données sur excel. Je m'explique je souhaite appliquer ce code :

VB:

Sub FormuleSheet1()
    ' Formule peut être acquise directement par l'enregistreur de macro
    Formule = "=IFERROR(INDEX(Feuil1!R2C3:R616000C24,MATCH(Sheet1!RC4&Sheet1!RC8,Feuil1!R2C1:R616000C1&Feuil1!R2C2:R616000C2,0),MATCH(Sheet1!R1C,Feuil1!R1C3:R1C24,0)),"""")"
    Sheets("Sheet1").[AE2].FormulaArray = Formule   ' On colle la formule
    Range("AE2").Copy                               ' On duplique cette formule sur la plage
    Range("AE2").AutoFill Destination:=Range("AE2:AE449835"), Type:=xlFillDefault
    Range("AE2:AE449835").AutoFill Destination:=Range("AE2:AZ449835"), Type:=xlFillDefault
    Range("AE2:AZ449835") = Range("AE2:AZ449835").Value     ' On colle les valeurs
    Range("AE2").Select
    Application.CutCopyMode = False
End Sub

L'objectif est de trouver des correspondances selon 3 critères (2 colonnes et une ligne).

Le code fonctionne très bien sur des petit jeu de données (max 30k lignes). Comme vous pouvez le voir je souhaite appliquer la formule sur 449k lignes sur 22 colonnes. Avez-vous une solution pour optimiser ce code pour que les fomurles puissent s'appliquer sur tout mon jeu de donnée ?

Je vous met également un fichier test.

Merci beaucoup d'avance.

Loïc DUBOIS · 7 Octobre 2023

Bonjour Cousinhub,

Je pense que je ne vais pas avoir le choix d'opter pour cette solution.

TooFatBoy · 7 Octobre 2023

Bonjour,

Sous cette forme ça semble être un peu moins lent que l'originelle de #1:

VB:

Sub FormuleSheet1()
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' La formule peut être acquise directement par l'enregistreur de macro
    Formule = "=IFERROR(INDEX(Feuil1!R2C3:R616000C24,MATCH(Sheet1!RC4&Sheet1!RC8,Feuil1!R2C1:R616000C1&Feuil1!R2C2:R616000C2,0),MATCH(Sheet1!R1C,Feuil1!R1C3:R1C24,0)),"""")"

    With Sheets("Sheet1").Range("AE2:AZ449835")
        .FormulaArray = Formule   ' On colle la formule
        .Value = .Value
    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

'    Range("AE2").Select

End Sub

Cousinhub · 8 Octobre 2023

Bonsoir,
Juste pour soumettre une idée, si tu veux persister dans ta volonté "VBA"...
Comme dans le 2ème onglet il y a énormément de doublons, que les 22 colonnes à récupérer sont dans l'ordre, et que tes formules matricielles ne vont te donner que la 1 ère ligne de la recherche...
Pourquoi ne pas faire une recherche matricielle sur 2 tableaux de 2 colonnes? (colonnes D:H de Sheet1 et A:B de Feuil1)
Et ainsi, obtenir un numéro de ligne? (donc sur une seule colonne)
Pour au final, en sortir un tableau VBA (qui comportera bien sûr moult doublons)...
Bon courage, et bon début de semaine

Loïc DUBOIS · 8 Octobre 2023

Hello, tout le monde,

merci pour vos messages. Au final, j'ai tenté de le faire via power query. Je pense que cela à très bien fonctionné (il faut que je vérifie, je verrai demain).

Bonne semaine à tous.

Loïc

XL 2019 Optimiser un code VBA dans un grand jeu de données

Loïc DUBOIS

XLDnaute Occasionnel

Pièces jointes

Loïc DUBOIS

XLDnaute Occasionnel

TooFatBoy

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Loïc DUBOIS

XLDnaute Occasionnel

Discussions similaires