VBA optimisation de code

Version imprimable

Bonjour,

Je pense que vous allez pouvoir m'aider.

Je cherche à optimiser la vitesse d'exécution d'une partie d'un programme VBA. Celui ci met presque 20 minutes!

La partie de programme en question :
------------------------
'A noter; nbl est le nombre de ligne dans tablcomplet (environ 2000), et lig le nombre de ligne dans tablecompil (environ 50 000). Pour chaque phase (p20, p30....) je dois retrouver dans tablcompil 4 valeurs. J'ai essayé avec des boucle for mais cela semble encore plus long

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
conca = ""
For a = 4 To nbl
 
        On Error Resume Next
        conca = Sheets("tablcomplet").Range("A" & a).Value & "p20"
 
    Sheets("tablcomplet").Range("AL" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AM" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AN" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("AO" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p30"
       Sheets("tablcomplet").Range("AP" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AQ" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AR" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("AS" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p40"
      Sheets("tablcomplet").Range("AT" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AU" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AV" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("AW" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p50"
      Sheets("tablcomplet").Range("AX" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AY" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AZ" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("BA" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p60"
      Sheets("tablcomplet").Range("BB" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("BC" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("BD" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("BE" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p70"
      Sheets("tablcomplet").Range("BF" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("BG" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("BH" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("BI" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
    Next

-----

J'ai également essayé de transformer un peu tout ça en passant par un tableau mais c'est encore pire! :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Dim rg As Range
Dim h As Variant
 
Set rg = Sheets("tablecompil").Range("S" & 2 & ":AF" & lig)
h = rg
 
 
For a = 4 To nbl
 On Error Resume Next
h(a - 2, 2) = Sheets("tablcomplet").Range("A" & a).Value & "p20"
h(a - 2, 3) = Sheets("tablcomplet").Range("A" & a).Value & "p30"
h(a - 2, 4) = Sheets("tablcomplet").Range("A" & a).Value & "p40"
h(a - 2, 5) = Sheets("tablcomplet").Range("A" & a).Value & "p50"
h(a - 2, 6) = Sheets("tablcomplet").Range("A" & a).Value & "p60"
h(a - 2, 7) = Sheets("tablcomplet").Range("A" & a).Value & "p70"
 
Next
 
       For a = 4 To nbl
        On Error Resume Next
 
 
    Sheets("tablcomplet").Range("AL" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 8, False)
    Sheets("tablcomplet").Range("AM" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 13, False)
     Sheets("tablcomplet").Range("AN" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 12, False)
      Sheets("tablcomplet").Range("AO" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 14, False)
 
 
       Sheets("tablcomplet").Range("AP" & a).Value = WorksheetFunction.VLookup(h(a - 2, 3), h, 8, False)
    Sheets("tablcomplet").Range("AQ" & a).Value = WorksheetFunction.VLookup(h(a - 2, 3), h, 13, False)
     Sheets("tablcomplet").Range("AR" & a).Value = WorksheetFunction.VLookup(h(a - 2, 3), h, 12, False)
 
etc..

Une idée?

Par avance merci

07/10/2017, 21h00
joe.levrai

Bonjour,

il me semble préférable d'utiliser la méthode FIND de l'objet Range

tu cherches la position de "conca" et ensuite tu récupères les valeurs décalées souhaitées (propriété Offset de l'objet Range)

nous avons répondu ces derniers jours sur plusieurs sujets traitant de la méthode FIND, je te laisse bûcher sur un début de code, reviens si ça coince ;)
07/10/2017, 21h14
benlesage

Merci à toi je regarde ça! FIND est moins gourmand?
07/10/2017, 21h19
joe.levrai

y'a débat, enfin y'a déjà eu débat à plusieurs reprises :mrgreen:

je dirais que oui, plus rapide, on verra ça :zoubi:
07/10/2017, 22h55
Philippe Tulliez

Bonjour,
Au lieu de faire une boucle (For a = 4 To nbl), insère en une seule instruction la fonction RECHERCHEV dans la plage de la colonne entière et ensuite si tu ne souhaites pas conserver la formule, tu remplaces, toujours en une seule instruction, la formule par son résultat.
Voir ces deux billets Ecrire une formule dans Excel à l'aide d'une procédure VBA et Comment remplacer des formules par leur résultat
08/10/2017, 00h44
eriiic
Bonjour,

sympas tes tutos Philippe, merci pour le travail.
J'ai une suggestion pour compléter le 2nd si tu veux bien.
En cas de formules nombreuses et/ou lourdes (sommeprod) il arrive qu'il y ait un déphasage et que l'on copie une partie non encore évaluée.
Il y a la propriété CalculationState (je ne sais pas depuis quelle version), on peut attendre son état correct pour copier :

Application.CalculationState, propriété
Cette propriété renvoie une constante XlCalculationState qui indique l'état de calcul de l'application, pour les calculs en cours dans Microsoft Excel. Type de données en lecture seule.
Code:

1 2 3 4 5 If Application.CalculationState = xlDone Then MsgBox "Done" Else MsgBox "Not Done" End If
Énumération XlCalculationState
Indique l'état de calcul de l'application.
Nom Valeur Description
xlCalculating 1 Les calculs sont en cours.
xlDone 0 Calculs terminés.
xlPending 2 Des modifications déclenchant un calcul ont eu lieu, mais le recalcul n'a pas encore été effectué.

Je te laisse voir si tu veux tester et l'ajouter, ou si tu préfères que je le mette en commentaire du tuto.
eric
08/10/2017, 05h26
unparia

Bonjour à tous
20 minutes pour rechercher environ 12000 valeurs parmi 50000 sur une feuille et écrire environ 48000 valeurs sur une autre feuille parait en effet long.

J'aimerais des réponses très précises aux questions suivantes qui concernent la feuille source
- 1) sa colonne S contient-elle des formules ?
- 2) sa colonne S est-elle triée ?
------ si pas triée : quelque raison s'oppose-t-elle à la trier ?
- 3) une ou plusieurs des valeurs conca y recherchées peut-elle s'y trouver plusieurs fois ?
- 4) une ou plusieurs des valeurs conca y recherchées peut-elle en être totalement absente ? (le on error resume next que je vois donne à penser que tel est le cas).

Ce sera en fonction de ces réponses, que je chercherais personnellement à déterminer le mécanisme le moins gourmand.

EDIT : ce qui me parait déjà certain, c'est que je réduirais significativement ma plage de recherche en filtrant la colonne S, puisque je ne m'intéresse qu'à celles de ses cellules de la 'forme' "*p?0"
il est à partir de là assez probable que :
1) - je ne "travaillerais" ensuite ni avec Find, ni avec Lookup, mais en boucle sur les seules lignes visibles (la plage résultante, donc)
- j'utiliserais dans celle boucle un bloc select case (pour les différents "conca") mais alors : si la colonne S ne contient pas de doublons "conca"
- j'arrêterais ma boucle dès que j'aurais tous mes "conca" (pourquoi la continuer pour rien ?)

- 2) SI je décidais néanmoins de travailler avec la méthode Find :
-- si de surcroît (mais ce ne serait qu'un petit bonus) la colonne S est triée je réduirais à chaque fois (à chaque conca cherché et trouvé) aux seules lignes au delà de celle de l'occurrence

Voilà, grosso modo et entre autres, la raison essentielle des questions que j'ai posées plus haut

Dans tous les cas de figure : je créerais une matrice de la plage de destination (dans l'autre feuille) et y écrirais mes valeurs "décidées". Ce ne serait qu'in fine, que j'en injecterais d'un bloc les valeurs dans la plage de destination.
Je suis à peu près certain de ce que l'on passera ainsi de 20 minutes à quelques secondes.;)
08/10/2017, 10h52
joe.levrai

Bonjour,

je n'avais pas vu que c'était une boucle 2 to 2000 :aie:

dans ce cadre, je me range sur la proposition de Philippe !
08/10/2017, 10h53
benlesage

Merci pour l'ensemble de vos réponses. Je pense que vais me mettre au boulot dès aujourd'hui en essayant, voire cumulant vos différentes solutions.

Pour répondre aux questions ci dessous

Citation:

Envoyé par unparia

Bonjour à tous
20 minutes pour rechercher environ 12000 valeurs parmi 50000 sur une feuille et écrire environ 48000 valeurs sur une autre feuille parait en effet long.

J'aimerais des réponses très précises aux questions suivantes qui concernent la feuille source
- 1) sa colonne S contient-elle des formules ? => oui elle contient une fomrule =CONCATENER(U3;"p";Y3). De même les colonnes 8,12,13,14 contiennent des formules par exe =SI(AD6>AE6;0;AE6-AD6) sur colonne 14 mais les valeurs associées ne bougent pas logiquement pendant la macro
- 2) sa colonne S est-elle triée ? Non elle n'est pas triée et elle peut l'etre sans souci
------ si pas triée : quelque raison s'oppose-t-elle à la trier ?
- 3) une ou plusieurs des valeurs conca y recherchées peut-elle s'y trouver plusieurs fois ? Les valeurs de conca n'ont qu'une occurrence dans cette colonne
- 4) une ou plusieurs des valeurs conca y recherchées peut-elle en être totalement absente ? (le on error resume next que je vois donne à penser que tel est le cas). oui une ou plusieurs peuvent être absentes d'où error resume next

Ce sera en fonction de ces réponses, que je chercherais personnellement à déterminer le mécanisme le moins gourmand.

EDIT : ce qui me parait déjà certain, c'est que je réduirais significativement ma plage de recherche en filtrant la colonne S, puisque je ne m'intéresse qu'à celles de ses cellules de la 'forme' "*p?0"
il est à partir de là assez probable que :
1) - je ne "travaillerais" ensuite ni avec Find, ni avec Lookup, mais en boucle sur les seules lignes visibles (la plage résultante, donc)
- j'utiliserais dans celle boucle un bloc select case (pour les différents "conca") mais alors : si la colonne S ne contient pas de doublons "conca"
- j'arrêterais ma boucle dès que j'aurais tous mes "conca" (pourquoi la continuer pour rien ?)

- 2) SI je décidais néanmoins de travailler avec la méthode Find :
-- si de surcroît (mais ce ne serait qu'un petit bonus) la colonne S est triée je réduirais à chaque fois (à chaque conca cherché et trouvé) aux seules lignes au delà de celle de l'occurrence

Voilà, grosso modo et entre autres, la raison essentielle des questions que j'ai posées plus haut

Dans tous les cas de figure : je créerais une matrice de la plage de destination (dans l'autre feuille) et y écrirais mes valeurs "décidées". Ce ne serait qu'in fine, que j'en injecterais d'un bloc les valeurs dans la plage de destination.
Je suis à peu près certain de ce que l'on passera ainsi de 20 minutes à quelques secondes.;)

J'espère avoir répondu à toutes tes interrogation. Merci à toi unparia

Bien
On y va par étapes :
1) commençons par filtrer la colonne S de la source
garde en vue la feuille source tablecompil et lance ce code puis vérifie que ne sont visibles que les lignes intéressantes pour la suite
Code:

1 2 3 4 5 With Worksheets("tablecompil") .Range("S2").AutoFilter Field:=1, Criteria1:="*p?0" MsgBox "ne reste-t-il que les lignes dont tu as besoin ?" .Range("S2").AutoFilter ' on remet pour l'instant les choses en l'état End With
On m'attend au restaurant. Je te lirai à mon retour

Cela marche mais en fait 98% des lignes rentrent dans cette catégorie. Pour être plus précis : p20 correspond à la phase 20 d'une gamme P30 phase 30 etc. La clé ****** devant pXXX est en fait le numéro de gamme Parfois il n'y a pas de phase 20. 98% des gammes comprennent moins de 10 phases. Je ne souhaite retenir que les 6 premières phases (env 85% des lignes) , les autres étant superflues.

Ajouter un filtre par phase avant chaque bloc de recherche de phase serait il efficace? Par ex :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 With Worksheets("tablecompil") .Range("S2").AutoFilter Field:=1, Criteria1:="*p20" End With Sheets("tablcomplet").Range("AL" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False) Sheets("tablcomplet").Range("AM" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False) Sheets("tablcomplet").Range("AN" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False) Sheets("tablcomplet").Range("AO" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False) With Worksheets("tablecompil") .Range("S2").AutoFilter ' on remet pour l'instant les choses en l'état End With

08/10/2017, 16h34
unparia

Citation:

Cela marche mais en fait 98% des lignes rentrent dans cette catégorie

Ouille ! Filtrer pour ne réduire la plage de recherche que de 2% ne vaut pas le coup.
Filtrer par phase : pas vraiment non plus !
Je vais donc réfléchir à la manière de grappiller quand même un peu, autrement, de ce "manque à gagner".
A ce soir.

Bonjour

triez tablecompil sur la colonne S.
Ainsi le P20 sera suivi du P30 éventuel, puis du P40, ...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 dim lignecompil as variant conca = "" For a = 4 To nbl Sheets("tablcomplet").Range("AL" & a & ":BI" & a).Value = "Absent" conca = Sheets("tablcomplet").Range("A" & a).Value & "p20" lignecompil = WorksheetFunction.Match(conca, Sheets("tablecompil").Range("S2:S" & lig), 0) if not IsError(lignecompil) then Sheets("tablcomplet").Range("AL" & a).value = Sheets("tablecompil").Range("Z" & lignecompil) Sheets("tablcomplet").Range("AM" & a).value = Sheets("tablecompil").Range("AE" & lignecompil) ... if Sheets("tablecompil").Range("S" & lignecompil +1) = Sheets("tablcomplet").Range("A" & a).Value & "p30" then Sheets("tablcomplet").Range("AP" & a).value = Sheets("tablecompil").Range("Z" & lignecompil + 1) ... if Sheets("tablecompil").Range("S" & lignecompil +2) = Sheets("tablcomplet").Range("A" & a).Value & "p40" then Sheets("tablcomplet").Range("AP" & a).value = Sheets("tablecompil").Range("Z" & lignecompil + 2) ... end if 'P40 end if 'P30 end if 'P20, if not IsError(lignecompil) next a
Je pense que ça limitera déjà beaucoup les vlookup et ira bien plus vite.
Si cela n'est pas assez rapide, il est possible de gagner du temps encore en triant tablcomplet sur la colonne a et au lieu du match, avoir un index de ligne sur tablecompil que l'on fait avancer tant qu'on n'a pas dépassé la valeur recherchée
Code:

1 2 3 4 5 6 7 lignecompil = 1 for a while Sheets("tablecompil").Range("S" & lignecompil) < concat lignecompil = lignecompil + 1 wend if Sheets("tablecompil").Range("S" & lignecompil) = concat then ...
Mais je ne pense pas que ce soit utile d'aller jusque là, 2 000 ligne <> 50 000, je ne pense pas que ce soit bien long à moins d'être très pressé. La première version est bien plus facile à suivre et débugguer, je ne pense pas que le gain de temps vaille la difficulté d'aller plus loin.

J'en profite pour attirer votre attention sur le fait qu'il n'est pas utile de faire "S" & 2 & ":AF" & lig, qu'il est plus simple d' écrire "S2:AF"

08/10/2017, 19h40
benlesage

Merci pour ton aide
Je viens d'essayer, j'ai la fenêtre de debogage avec erreur 1004 sur lignecompil = WorksheetFunction.Match(conca, Sheets("tablecompil").Range("S2:S" & lig), 0) (lorsque l'on arrive sur une ligne sans phase 20).
08/10/2017, 23h30
RyuAutodidacte

Bonsoir,

je suis assez visuel pour coder.
Dans la colonne S y a t'il des doublons ??
En tout cas vu le nombre de colonnes entre les 2 feuilles il y a pas mal de données !

j'ai bien une petite idée … mais il faut juste que j'arrive à bien entrevoir la structure du fichier
un descriptif détaillé serait un plus …
08/10/2017, 23h33
benlesage

Non effectivement il n'y a pas de doublons. Ce sont des clés uniques

juste pour test et principe de recherche

remplacer le texte de MaValeur par une valeur existante de la colonne S => le principe récupérer le N° de ligne pour faire coïncider les valeurs à trouver
(PS : il faudra bien sur au final faire une gestion d'erreur)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Test()
Dim i&, Coll As New Collection, T!, MaValeur$
    T = Timer
    VA = Sheets("tablecompil").Range(Cells(1, "S"), Cells(Rows.Count, "S").End(xlUp)).Value
    For i = LBound(VA, 1) To UBound(VA, 1)
        Coll.Add i, CStr(VA(i, 1))
    Next
    MsgBox Format(Timer - T, "0.000 s")
    MaValeur = "15000P20" ' texte à remplacer par une valeur existante en colonne S
    MsgBox "La ligne à touver est : " & Coll(MaValeur)
 
    'on se servira de ces Arrays (cf ci-dessous) il faut juste agencé le tout
'    TBConcat = Array("p20", "p30", "p40", "p50", "p60", "p 70")
'    ColTBCompil = Array(8, 13, 12, 14)
'    ColTBComplet = Array("AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI")
 
End Sub

Edit : on passera par tableau bien sur pour accélérer le processus

08/10/2017, 23h55
benlesage
Lorsque je remplace la valeur j'ai une erreur de syntaxe
Code:

1 2 3 4 5 Sub Bouton1_Cliquer() Sub Bouton1_Cliquer() Dim i&, Coll As New Collection, T!, 1852p20$ T = Timer T = Timer
j'ai essayé aussi

Code:

Dim i&, Coll As New Collection, T!, 1852p20

il faut remplacer en ligne 9 "15000P20" par une quelconque valeur de la la colonnes qui se finit par … p20 ou autre p…

Edit :
Le principe est d'utiliser une collection pour la recherche pour trouver directement la ligne correspondante
si la valeur existe on a la ligne pour mettre les valeurs qui correspondent, si on ne trouve pas cela provoque une erreur et donc on passe

Edit 2: voilà un code complet à tester (selon les indications du de la discussion …

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub Test()
Dim i&, j&, k As Byte, VA, Coll As New Collection, TBConcat, T!, DL_CpltA&, Tab_CpltA, Tab_Cplt, L&
 
    T = Timer
    With Sheets("tablecompil")
        With .Range(.Cells(1, "S"), .Cells(.Cells(.Rows.Count, "S").End(xlUp).Row, "AF"))
            VA = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,8,13,12,14}]) 'à utiliser si -65000 lignes et de poussières dans la base
            'on stocke les valeurs à chercher de la col S et les valeurs à mettre des colonnes 8 , 13 , 12 , 14 dans la variable tableau VA
        End With
    End With
    For i = LBound(VA, 1) To UBound(VA, 1)
        Coll.Add i, CStr(VA(i, 1)) 'ici je n'ai pas fait de gestion d'erreur car on est censé avoir des ID uniques (selon la réponse à ma question)
    Next
 
    TBConcat = Array("p20", "p30", "p40", "p50", "p60", "p70") 'Array pour concaténage
 
    With Sheets("tablcomplet")
        DL_CpltA = .Cells(.Rows.Count, "A").End(xlUp).Row 'Dernière ligne en col A
        Tab_CpltA = .Range(.Cells(1, "A"), .Cells(DL_CpltA, "A")).Value ' Variable tableau des valeurs à chercher
        Tab_Cplt = .Range(.Cells(1, "AL"), .Cells(DL_CpltA, "BI")).Value ' Variable tableau des colonnes incriminées à remplir si valeur trouvée
 
        On Error Resume Next 'la gestion d'erreur dans le cas où on ne trouve pas la valeur cherchée
        For i = 2 To DL_CpltA
            k = 1 ' k est là afin de faire les correspondances des colonnes AL à BI
            For j = LBound(TBConcat) To UBound(TBConcat)
                L = Coll(Tab_CpltA(i, 1) & TBConcat(j)) ' récupération de la ligne dans tablecompil
                If Err Then
                    Err.Clear
                Else
                    Tab_Cplt(i, k) = VA(L, 2):    Tab_Cplt(i, k + 1) = VA(L, 3):    Tab_Cplt(i, k + 2) = VA(L, 4):    Tab_Cplt(i, k + 3) = VA(L, 5) 'on remplit le tableau Tab_Cplt
                End If
                k = k + 4 ' on incrémente k pour faire correspondre chaques colonnes (AL à BI) à chaques TBConcat(j)
            Next
        Next
        On Error GoTo 0
        Application.ScreenUpdating = False
            .Range("AL1").Resize(UBound(Tab_Cplt, 1), UBound(Tab_Cplt, 2)) = Tab_Cplt
        Application.ScreenUpdating = True
    End With
    MsgBox "Temps afin de stocker dans une collection toutes les valeurs à trouver  et de donner les résultats ensuite : " & Format(Timer - T, "0.000 s")
End Sub

09/10/2017, 17h54
benlesage

1.842 secondes!! et ça semble marcher!! vraiment impresionnant. Je peux même me permettre du rab en ajoutant quelques autres phases complémentaires du coup.

Merci!
09/10/2017, 18h22
RyuAutodidacte

Bonjour,

Après avoir bien vérifier l'intégrité du résultat / données, si tout est ok,
ne pas oublier de clôturer la discussion et de voter pour les posts ayant permis de faire avancer la discussions … !

PS : Il est inutile de répéter le post précédent dans dans une réponse, et ceci afin que l'on puisse avoir une meilleure lisibilité lors de la lecture de la discussion … ;)
09/10/2017, 22h24
RyuAutodidacte

Coucou Jacques,

+1 :plusser: pour ton post #7
Hier soir trop fatigué pour lire correctement et comme j'ai fait mon code très très tard (à 2-3 h du matin),
et je me rend compte ce soir des questions pertinentes et de la direction de la matrice … :oops:

Amicalement

Ryu
09/10/2017, 23h19
unparia

Bonjour Ryu
J'ai cessé d'intervenir dès que je t'ai vu prendre un chemin efficace.
Je te félicite d'avoir pensé à utiliser des arrays comme tu l'as fait. C'est là la marque d'un dégourdi :plusser:
Amitiés
10/10/2017, 09h14
RyuAutodidacte

Merci 8-)
Je suppose que tu as vu le clin d'œil à l'incrémentation en ligne (vu sur un autre post) avec celle de l'incrémentation en colonne ici ;) . Très pratique !!
10/10/2017, 09h51
Marc-L

Salut !

On paie mal un maître en ne restant toujours que l’élève. (Nietzsche)

(Ryu a été un bon élève et depuis il vole de ses propres ailes ! 8-))
10/10/2017, 10h43
RyuAutodidacte

Coucou Marc,

Et moi j'ai envie de dire que l'on peut être sûr d'être un bon élève qu'avec un bon maître ;)
Ce que tu es :ccool:
(Bien sûr à condition d'y mettre du sien évidemment, le travail paie:D)
:plusser: