Problème lookup en mode vba

Version imprimable

09/10/2017, 14h18
magot98

1 pièce(s) jointe(s)

Problème lookup en mode vba

Bonjour,

J'ai un fichier qui contient beaucoup de cellules avec calcul de type recherchev. Par ailleurs, il s'agit de concatener les valeurs correspondantes dans une seule cellule. J'ai trouvé la fonction MYLOOKUP qui fait exactement ce que je souhaite : https://www.extendoffice.com/documen...-one-cell.html (la version améliorée par Jay Coltrain c.f. commentaires en base de page.

Voir mon fichier Excel en annexe, onglet 'mylookup'. C'est LE résultat que j'espérais, mais avec plusieurs 100aines de cellules de ce genre, les calculs deviennent beaucoup trop lents.

Question: y-a-t'il un moyen d'arriver au même résultat, mais avec un processus de (re-)calcul beaucoup plus rapide? J'ai trouvé cette information: https://stackoverflow.com/questions/...ves-to-vlookup, avec la fonction vbalookup. Il semblerait qu'en passant par un dictionnaire VBA en effet, cela soit 100x plus rapide. Cette méthode m'intéresse, mais:

1. J'obtiens #valeur! si je définis une plage de recherche de plus de 34 lignes, ce qui n'est pas pratique (c.f. fichier annexé, onglet 'vbalookup' pour un exemple concret)
2. Cette fonction ne concatène pas les résultats comme dans mylookup

Help!

Merci d'avance pour vos suggestions

Pièce jointe 318070
09/10/2017, 14h22
Jean-Philippe André

Salut,

pour des raisons de securite entreprise, je suis dans l'incapacite de telecharger ton fichier en PJ.

POurrais-tu nous en dire plus en ecrivant directement ce que tu cherches a faire, et nous copier le code directement pour que l'on en sache un peu plus stp ?

:cristal:

2 pièce(s) jointe(s)

Bonjour Jean-Philippe André,

Voici le code MYVLOOKUP qui fonctionne actuellement. Je cherche à obtenir exactement le même résultat, mais plus vite (chaque fois que j'ajoute une ligne, toutes mes cellules avec la fonction MYVLOOKUP procède à une recalculation, ce qui prend un max de temps):

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
Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
'Updated 6/9/16 Jay Coltrain
'Dim rng As Range
Dim xResult As String
xResult = ""
 
Dim Rows As Long, i As Long
Rows = pWorkRng.Rows.Count
 
For i = 1 To Rows
If pWorkRng.Cells(i, 1).Value = pValue Then
xResult = xResult & ", " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1)
End If
Next i
 
Dim varSection As Variant
Dim sTemp As String
Dim sDelimiter As String
 
sDelimiter = ","
 
For Each varSection In Split(xResult, sDelimiter)
 If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then
 sTemp = sTemp & sDelimiter & varSection
 End If
Next varSection
 
 
MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1)
End Function

Cela me permet d'obtenir ce résultat:

Pièce jointe 318100

Voici le code vba qui faire appel à un dictionnaire :

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
Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
  Dim dict As New Scripting.Dictionary
  Dim myRow As Range
  Dim i As Long, J As Long
  Dim vResults() As Variant
 
  ' 1. Build a dictionnary
  For Each myRow In refRange.Columns(1).Cells
    ' Append A : B to dictionnary
    dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
  Next myRow
 
  ' 2. Use it over all lookup data
  ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
  For i = 1 To lookupRange.Rows.Count
    For J = 1 To lookupRange.Columns.Count
      If dict.Exists(lookupRange.Cells(i, J).Value) Then
        vResults(i, J) = dict(lookupRange.Cells(i, J).Value)
      End If
    Next J
  Next i
 
  vbalookup = vResults
End Function

Et voici le résultat que j'obtiens:

Pièce jointe 318095

09/10/2017, 15h30
Jean-Philippe André

Ok

tu es sur un perimetre de combien de lignes au total ?

Tu devrais passer par des tableaux au lieu de passer par des range deja =]
http://didier-gonard.developpez.com/...s-tableau-vba/
09/10/2017, 15h45
magot98

Alors pour l'instant seulement 110 lignes, mais la formule est dans 3 colonnes. C'est déjà super lent, et cela ne va pas s'arranger puisqu'il est prévu d'utiliser cette liste sur le long terme (plusieurs milliers de lignes).