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

Loïc DUBOIS

XLDnaute Occasionnel
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.
 

Pièces jointes

  • test fofo2.xlsx
    19.1 KB · Affichages: 8

TooFatBoy

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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
 

Statistiques des forums

Discussions
313 243
Messages
2 096 509
Membres
106 645
dernier inscrit
hf10