Discussion :

[benlesage]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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

[benlesage]

Merci à toi je regarde ça! FIND est moins gourmand?

[joe.levrai]

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

je dirais que oui, plus rapide, on verra ça

[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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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.

[joe.levrai]

Bonjour,

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

dans ce cadre, je me range sur la proposition de Philippe !

[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

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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[benlesage]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[unparia]

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.

[Gfacro]

Bonjour

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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"

[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).

[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 …

[benlesage]

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

[RyuAutodidacte]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[benlesage]

Lorsque je remplace la valeur j'ai une erreur de syntaxe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[RyuAutodidacte]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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!