VBA: Recherchev Plusieurs Résultats
J'ai besoin d'un peu d'aide avec un peu de code.
Je suis en train d'effectuer une Recherchev, et les données sont affichées dans les colonnes O, P et Q.
Ce que je suis en train de faire est de la boucle si la feuille("Global") de colonne d'Un départ à la ligne 3 jusqu'à la dernière utilisation de la ligne. Il a besoin de faire correspondre les données dans la feuille (les"Détails") dans la colonne de départ à la ligne 2.
Alors, quand il trouve une valeur correspondante, il affiche les résultats de "Détails" C2 "Global" O2 "Détails" I2 "Global" de la P2 et de "Détails" G2 "Global" T2.
Il doit alors en boucle, même si les "Global" d'appariement et de copier toutes les données. Si aucune correspondance n'est trouvée, l'affichage "NA!".
La dernière chose dont j'ai besoin pour faire est de supprimer toutes les lignes dans Global, où un match n'a pas été trouvé.
Le code que j'ai ci-dessous fait ce que j'ai besoin, le seul problème est qu'il est incroyablement lent, le procès-verbal de la boucle si 800 lignes, parfois même plus!!
Est-il une autre façon de le faire, ce qui sera plus fluide et plus rapide?
Toute aide est très appréciée!!
Merci
`Private Sub btnVlookUp_Click()
Dim i, j, lastG, lastD As Long
' find last row
lastG = Sheets("Global").Cells(Rows.Count, "B").End(xlUp).Row
lastD = Sheets("Details").Cells(Rows.Count, "A").End(xlUp).Row
' loop over values in "Global"
For i = 3 To lastG
lookupVal = Sheets("Global").Cells(i, "B") ' value to find
' loop over values in "details"
For j = 2 To lastD
currVal = Sheets("Details").Cells(j, "A")
If lookupVal = currVal Then
Sheets("Global").Cells(i, "O") = Sheets("Details").Cells(j, "C")
Sheets("Global").Cells(i, "P") = Sheets("Details").Cells(j, "I")
Sheets("Global").Cells(i, "Q") = Sheets("Details").Cells(j, "G")
' mark the row
Sheets("Details").Cells(j, "Z") = "marked"
End If
Next j
Next i
' loop over rows in "details" and delete rows which have not been marked
For j = 2 To lastD
If Sheets("Details").Cells(j, "Z") <> "marked" Then
' delete unmarked rows
Sheets("Details").Cells(j, "A").EntireRow.Delete
If Sheets("Details").Cells(j, "B") <> "" Then
j = j - 1 ' revert iterator so it doesn't skip rows
End If
Else:
' remove the mark
Sheets("Details").Cells(j, "Z") = ""
End If
Next j
End Sub`
OriginalL'auteur atame | 2015-08-04
Vous devez vous connecter pour publier un commentaire.
Avec les conseils ici, et beaucoup d'essais et d'erreurs, j'ai réussi à modifier mon code.
J'ai testé cela sur plus de 600 dossiers et il s'exécute en quelques secondes, où il aurait pris quelques minutes sur le code précédent.
Si vous pouvez voir une meilleure façon de faire le code ci-dessous, alors laissez-moi savoir, je suis encore à apprendre VBA donc de toute l'aide que je peux obtenir la meilleure!!!
Merci à tous pour le soutien!!!!!!!!
OriginalL'auteur atame
Votre code est très inefficace, comme l'écrit, c'est pourquoi cela prend une éternité. Vous n'avez pas mentionné spécifiquement le nombre de lignes dans votre "Global" et "Détail" feuilles (vous l'avez mentionné, 800, ne sais pas si c'est à la fois). Mais si il y avait 1000 dans chaque cas, votre deux boucles sont 1000x1000 = 1 million de cycles.
La meilleure solution est de ne pas utiliser VBA, mais l'utilisation de la fonction RECHERCHEV d'Excel. Voici ce que vous devez faire:
Trier les Détails de la feuille par Une Colonne
Ensuite, dans le Global de la feuille, dans la cellule de l'O3, vous allez mettre la formule suivante:
=RECHERCHEV(A3,les Détails!$A2:$I(quelle que soit la dernière ligne est),3,FAUX)
Dans le cas où vous n'êtes pas familier avec cette fonction, il faut répondre au premier argument, regarde vers le haut dans la première colonne de la deuxième argument jusqu'à ce qu'il trouve une correspondance, puis renvoie la valeur de la ligne à la colonne de la troisième argument. Le dernier "FAUX" ne vous donne qu'une correspondance exacte, sinon vous aurez une #NA (si vous utilisez la valeur TRUE, vous aurez la correspondance la plus proche à la place).
Ensuite copier cette formule en bas de la feuille entière.
Puis copier la colonne, et de coller des valeurs. Cela se débarrasse de la forumlas et ne laisse que le valeurs, ce qui rend tout beaucoup plus rapide.
Ensuite trier la table par rapport à cette colonne, et tous les #NA va tomber, et vous pouvez supprimer le tout en une seule opération.
Si vous voulez le faire via VBA, les étapes ci-dessus peuvent facilement être codées:
C'est un début, mais devrait vous permettre de continuer. Bonne chance!
OriginalL'auteur hpf
Il ya un couple de choses que vous pouvez faire facilement la vitesse de votre code.
Tout d'abord, si vous ajoutez la ligne
Application.ScreenUpdating = False
au début de votre code, il s'arrête Excel d'avoir à faire tout le scintille et clignote que vous voyez lors de l'exécution de votre code (qui est en fait l'ajout de ces valeurs une par une, la suppression de lignes, etc. tout ce qui prend beaucoup de temps).Ensuite, vous pouvez ajouter un
Exit For
à la fin de votreIf
déclaration (à droite avant votreEnd If
). Cela va arrêter le imbriqués Pour la Boucle pour éviter de courir à travers toutes les données lorsque vous avez déjà trouvé ce que vous cherchez.Enfin, je sais que vous utilisez
j = j - 1
à définir votre itérateur de ne pas sauter de lignes, mais une meilleure pratique consiste à la place de travail de la direction opposée. Si vous modifiez la Boucle for Pour lireFor j = lastD to 2 Step -1
il va faire la Boucle courir dans le sens inverse, de sorte que les lignes supprimées ne sont pas un problème et vous pouvez supprimer le "reset" de la ligne (ce sera à peine la vitesse de votre code, c'est juste plus d'une suggestion de la façon de traiter ce problème commun).OriginalL'auteur TMH8885