Demande d'optimisation de mon code vba

[napo124]

Bonjour,

Je travaille sur un projet, et je suis amené à préparer une macro de tri de données.
Celle ci doit éffectuer plusieurs test, ligne par ligne.

Deux lignes ayant le même fournisseurs et le même ID est considéré comme DOUBLON.
La base de données est triée par ID, puis par fournisseurs et enfin par "ref" (cf fichier)

La base de données sera constituée de plusieurs milliers de lignes. Ma macro est fonctionnel, mais commence à être vraiment longue au bout de 10 000 lignes.
Pouvez vous m'aider à rendre mon code plus rapide?

Le schéma décisionnel est présenté dans le fichier. Le code vba est détaillé avec mes commentaires.

Pouvez vous m'aider ?

Cordialement

 

[napo124]

Re : Demande d'optimisation de mon code vba

A vrai dire mon code final est très long et comporte beaucoup d'actions de mise en forme, de tri, de sélection de données, de copier/coller, d'ouverture de classeurs Excel,...

Cela risque d'embrouiller tout le monde

Voici ce que j'ai fais:

J'ai positionné toutes les lignes dont le statut est "9" en bas de ma base (via un tri des données, puisque 9 est la valeur maximum de la colonne). J'ai utilisé MATCH pour trouver la première ligne ayant le statut "9". J'ai dimensionné la variante tableau jusqu'à cette ligne.

Puis, les données étant triées par ID puis par Fournisseurs puis par REF, je fais le raisonnement suivant, ligne par ligne (boucle do):

si la ligne suivante ou précèdente a le meme ID ET le meme fournisseur, alors c'est une classe "multi" (doublon), sinon "mono" (et fin du traitement).

Si c'est un "multi" et que la ligne précédente n'a pas le meme ID ou le meme fournisseur, alors c'est le PREMIER doublon de son genre dans la base de données. Je dois donc calculer le min de ce type de doublons. Si la ligne précédent est en revanche identique, alors je me contente de reprendre le min qu'on aura déjà calculée.

Boucle do qui va comparer les valeurs des priorités des lignes suivantes jusqu'à rencontrer une ligne dont l'ID ou le fournisseur est différent (auquel cas, ce ne sera plus le meme doublon!) OU que le ref soit un "o" (qui nous intéresse pas). Cela me permet de déterminer le min de ce type de doublons que j'affecte à la colonne "min" de la ligne en cours.

FIN DES TESTS

Il ne me reste plus qu'à vérifier si la ligne est un MONO ou si son MIN est identique à sa priorité ou si c'est un ref "o". Au quel cas, je garde la ligne. Sinon je la supprime.

Je remplace ma base de données par mon tableau !

Voilà comment mon code fonctionne. Avec des boucles do et des si.

Pertinent?

 

[Si...]

Re : Demande d'optimisation de mon code vba

salut

essaie de remplacer tous les Zone.Cells par Zone tout simplement et dis nous.

 

[Jam]

Re : Demande d'optimisation de mon code vba

salut Napo, GeoTrouvePas, Si...

Tu veux pas nous mettre à dispo ton code qu'on y jette un oeil, ça ira beaucoup plus vite pour te donner les bons conseils/modifs, etc... ?

Bon courage,

 

[napo124]

Re : Demande d'optimisation de mon code vba

Bonjour Jam, SI..., Geotrouvepas,

Voici la partie du code qui fait le gros du travail (pour mieux la comprendre, mon dernier poste en explique la logique).

La base de données est assez grande (environ 35 colonnes sur un nombre variable de lignes).

Les colonnes:
1: ID (si meme ID et meme fournisseur, alors DOUBLON)
2: Validation (ok ou suppr) à compléter par la macro
3: Classe (multi ou mono) à compléter par la macro
4: MIN (du groupe du doublon) à compléter par la macro
w = 32 numéro du fournisseur (si meme ID et meme fournisseur, alors DOUBLON)
x = 27 'Numero de la Priorités (dont on cherche le minimum pour chaque groupe de doublons)
y = 28 'Numero de la colonne statut (4, 6 ou9) si 9 alors à supprimer
z = 39 'Numero de la colonne TOP REF ("o" ou "n")

Les inconnues:
g= numéro de la ligne du premier statut 9 (la base de données est triée sur les statuts, les 9 à la fin).
a= numéro de la ligne en cours
b= numéro de la ligne en cours (pour la boucle qui doit déterminer le min du doublon).
d= min du groupe de doublons

a = 1
b = 0
d = 0
e = 0

w = 32 'Numero de la colonne Société
x = 27 'Numero de la colonne Priorités
y = 28 'Numero de la colonne statut
z = 39 'Numero de la colonne TOP REF

Zone.Cells(c + 1, w) = 99189998
Zone.Cells(c + 1, y) = 9

g = Application.Match(9, Zone.Range(Cells(1, y), Cells(c + 1, y)), 0)


Zone.Cells(1, 32) = "Code Fournisseur C."



TABL = Zone.Range(Cells(1, 1), Cells(g + 1, 45)).Value


Do
a = a + 1


If (TABL(a, 1) = TABL(a - 1, 1) And TABL(a, w) = TABL(a - 1, w)) Or (TABL(a, 1) = TABL(a + 1, 1) And TABL(a, w) = TABL(a + 1, w)) Then
TABL(a, 3) = "MULTI"

If TABL(a - 1, w) = TABL(a, w) And TABL(a - 1, 1) = TABL(a, 1) Then
TABL(a, 4) = TABL(a - 1, 4)

Else

b = 0
d = 0

Do
b = b + 1

If TABL(a + b - 1, x) >= TABL(a + b, x) And TABL(a + b, z) = "N" Then
d = TABL(a + b, x)
Else
d = TABL(a + b - 1, x)
End If

Loop Until TABL(a + b + 1, w) <> TABL(a, w) Or TABL(a + b + 1, 1) <> TABL(a, 1) Or TABL(a + b + 1, z) = "O" Or (a + b + 1) = g

TABL(a, 4) = d

End If

Else
TABL(a, 3) = "MONO"
TABL(a, 4) = "ND"

End If




If TABL(a, 3) = "MONO" Or TABL(a, 4) = TABL(a, x) Or TABL(a, z) = "O" Then
TABL(a, 2) = "ok"
Else
TABL(a, 2) = "suppr"
End If






Loop Until a = g - 1

Zone.Range(Cells(1, 1), Cells(g + 1, 45)).Value = TABL

Zone.Range(Cells(g, 2), Cells(c + 1, 2)).Value = "suppr"
Zone.Range(Cells(g, 3), Cells(c + 1, 4)).Value = "ND"


Zone.Range(Cells(c + 1, 1), Cells(c + 1, 35)).Select
Selection.ClearContents

N'hésitez pas à me demander des précisions!

 

[Jam]

Re : Demande d'optimisation de mon code vba

Salut Napo, le forum,

Tu trouveras ci-après un code pas beaucoup plus optimisé sauf sur quelques points les With...End With principalement. Je pense aussi que ton code gagnera en clarté (ça fait aussi parti d'une forme d'optimisation: relire vite et comprendre facilement le code) en utilisant des variables "parlantes" et ça ne ralenti pas le code

a = 1
b = 0
d = 0
e = 0

'w = 32 'Numero de la colonne Société
'x = 27 'Numero de la colonne Priorités
'y = 28 'Numero de la colonne statut
'z = 39 'Numero de la colonne TOP REF

colSociété = 32
colPriorité = 27
colStatut = 28
colTopRef = 39

With Zone
	.Cells(c + 1, colSociété) = 99189998
	.Cells(c + 1, colStatut) = 9
	g = Application.Match(9, .Range(Cells(1, colStatut), Cells(c + 1, colStatut)), 0)
	.Cells(1, 32) = "Code Fournisseur C."
	TABL = .Range(Cells(1, 1), Cells(g + 1, 45)).Value
End With

Do
	a = a + 1
	If (TABL(a, 1) = TABL(a - 1, 1) And TABL(a, colSociété) = TABL(a - 1, colSociété)) Or (TABL(a, 1) = TABL(a + 1, 1) And TABL(a, colSociété) = TABL(a + 1, colSociété)) Then
		TABL(a, 3) = "MULTI"
		If TABL(a - 1, colSociété) = TABL(a, colSociété) And TABL(a - 1, 1) = TABL(a, 1) Then
			TABL(a, 4) = TABL(a - 1, 4)
		Else
			b = 0:d = 0
			Do
				b = b + 1
				If TABL(a + b - 1, colPriorité) >= TABL(a + b, colPriorité) And TABL(a + b, colTopRef) = "N" Then
					d = TABL(a + b, colPriorité)
				Else
					d = TABL(a + b - 1, colPriorité)
				End If
			Loop Until TABL(a + b + 1, colSociété) <> TABL(a, colSociété) Or TABL(a + b + 1, 1) <> TABL(a, 1) Or TABL(a + b + 1, colTopRef) = "O" Or (a + b + 1) = g
			TABL(a, 4) = d
		End If
	Else
		TABL(a, 3) = "MONO"
		TABL(a, 4) = "ND"
	End If

If TABL(a, 3) = "MONO" Or TABL(a, 4) = TABL(a, colPriorité) Or TABL(a, colTopRef) = "O" Then
	TABL(a, 2) = "ok"
Else
	TABL(a, 2) = "suppr"
End If

Loop Until a = g - 1
With Zone
	.Range(Cells(1, 1), Cells(g + 1, 45)).Value = TABL
	.Range(Cells(g, 2), Cells(c + 1, 2)).Value = "suppr"
	.Range(Cells(g, 3), Cells(c + 1, 4)).Value = "ND"
	.Range(Cells(c + 1, 1), Cells(c + 1, 35)).ClearContents
End With

Je réfléchis (lentement car je bosse en même temps) à différentes solutions, et je me demandais, mais il faut que je comprenne bien l'objectif de ton programme, si tu ne pouvais pas passer par des requêtes (via ADO par exemple) pour sélectionner les enregistrements avant de les marquer par Suppr, etc...

Bon courage,

 

[Fo_rum]

Re : Demande d'optimisation de mon code vba

Bonjour,

comme Si... le sous entendait, Zone est déjà un tableau donc il suffit de travailler avec lui.

Ce n'est pas le nombre de colonnes impliquées qui prennent du temps mais plutôt le nombre de lignes.
Tu aurais pu nous donner 2 ou 3 lignes types pour que l'on étudie plus rapidement ta macro.

 

[napo124]

Re : Demande d'optimisation de mon code vba

Merci à tous pour vos réponses et de votre implication!

Voilà le fichier avec les données principales. J'ai enlevé la partie qui ne nous intéressait pas dans le code, et j'ai gardé la MAJ de Jam.
La macro est fonctionnelle normalement et vous pouvez voir le résultat attendu (avec le processus décisionnel sur l'onglet 2).

 

[napo124]

Re : Demande d'optimisation de mon code vba

Pas d'idée ?

 

[pierrejean]

Re : Demande d'optimisation de mon code vba

Bonjour a tous

Un essai

 

[napo124]

Re : Demande d'optimisation de mon code vba

Bonjour à tous,

Merci à tous pour vos contributions!
Je n'ai pas réussi à réduire le temps au maximum, mais grace à votre aide combinée, j'ai pu au moins réduire d'un bon tiers le temps opératoire!

Une dernère petite question pour la route.
J'ai un champs Prix rentionné avec des "." au lieu de ",". J'utilise donc la fonction "rechercher" et "remplacer" mais celle-ci présente un dysfonctionnement sur un grand nombre de lignes!

Bizarement: ".4560" est transformé en ",4560" mais "5.450" est transformé en "5 450" (???): les chiffres avant la virgules suppriment la virgule dans le résultat. Etrange non? J'ai esayé avec d'autres astuces, mais le résultat est le même. Avez vous une idée?

 

[pierrejean]

Re : Demande d'optimisation de mon code vba

Re
Peux-tu nous faire part de ta derniere macro ?
Peux-tu nous poster un exemple de dysfonctionnement ?

 

[Jam]

Re : Demande d'optimisation de mon code vba

Bonjour Napo, Pierrejean,

Pour ma part j'utiliserai plutôt le menu Convertir !
Il suffit de sélectionner la colonne, aller sur le menu Convertir, faire suivant jusqu'à l'étape 3 (dans XL2K7 mais je crois que c'est la même chose dans XL2K3) et d'indiquer dans l'option Avancé les paramètres adéquats. Valider par Termine, et c'est tout bon.

-> Pour ton problème, je pencherai pour un format de cellule. Celui-ci est peut-être différent entre les cellules
Bon courage

 

[napo124]

Re : Demande d'optimisation de mon code vba

Bonjour à tous!
Je pense que Jam avait raison!
J'ai utilisé une fonction "cnum + recherche du caractère "." dans la cellule + concatener " pour reformer le prix avec la virgule et en tant que chiffre. La fonction appliquée à toute la colonne fonctionne bien une fois inscrite dans le code vba.

Voilà je pense être satisfait du résultat !
Encore une fois, merci à vous tous pour vos efforts et votre disponibilité!