Remplacement de données multiples

[PJstef]

Bonjour à tous.

Je suis nouveau sur le forum, et après avoir passé en revue beaucoup de topics, je n'ai pas trouvé de solution à mon problème, qui est le suivant :

Je vous fait parvenir un fichier "EXEMPLE XLD" pour aider la compréhension.

J'ai une base de données de commandes clients (onglet "base") comprenant 2 colonnes (E et F) chiffrées en €.
Parmi les valeurs de ces colonnes, certaines sont erronées et doivent être remplacées par des valeurs déterminées dans un autre onglet ("conv").

Dans ce deuxième onglet, les valeurs "CIBLE" sont celles qu'il faut remplacer, et les valeurs "REEL" sont celles qui doivent apparaître à la place.

Dans la mesure où l'onglet "base" va être régulièrement alimenté avec un extract d'une BDD, le valeurs fausses réapparaitront, et devront être remplacées à chaque fois.

L'opération à réaliser est donc :

Pour chaque cellule non vide des colonnes E et F de l'onglet "base",
si la valeur de la cellule étudiée est égale à la valeur de l'une des cellules CIBLES de l'onglet "conv",
alors il faut la remplacer par la valeur de la cellule REEL correspondante.
puis recommencer le test avec la cellule du dessous

OU ALORS

pour chaque cellule non vide de la colonne "CIBLE" de l'onglet "conv"
si la valeur de cette cellule est égale à la valeur de l'une des cellules des colonnes E et F de l'onglet "base"
alors remplacer la valeur de ces cellules par la valeur "REEL" correspondante dans l'onglet "conv"


Je suis parvenu à le faire pour la première ligne, mais je suis bloqué car je ne sais pas comment faire pour relancer l'opération pour chaque cellule, même avec un Do while + Loop.

J'ai pensé le faire en utilisant 2 cellules actives (une dans chaque onglet) mais je n'ai rien trouvé à ce sujet...

Je m'en remets donc à vous et à vos idées!

N'hésitez pas à demander une reformulation si quelque chose ne vous semble pas clair.

Merci d'avance.

 

[job75]

Re : Remplacement de données multiples

Bonjour PJstef, bienvenue sur XLD,

C'est très simple, il suffit d'utiliser la commande "Remplacer" en VBA :

Sub Remplacer()
'Feuil1 et Feuil2 sont les CodeNames des feuilles
Dim c As Range
Application.ScreenUpdating = False
For Each c In Feuil2.[A:A].SpecialCells(xlCellTypeConstants)
  Feuil1.[E:F].Replace c, c(1, 2), xlWhole
Next
End Sub

Edit : Application.ScreenUpdating = False peut faire gagner du temps si la liste en Feuil2 est grande.

A+

 

[PJstef]

Re : Remplacement de données multiples

Bonjour job75

Merci beaucoup pour ta réactivité.!
Je vais essayer d'appliquer ce code sur le champ.

 

[PJstef]

Re : Remplacement de données multiples

Merci encore job75, ce code fonctionne à la perfection.

Et dire que ça fait 2 jours que je tourne en rond avec des dizaines de lignes de code... 🙂

 

[job75]

Re : Remplacement de données multiples

Re,

Pour info j'ai testé avec 1000 valeurs cibles en Feuil2 et 10000 valeurs remplacées sur 10000 lignes en Feuil1.

Sur Win 7 - Excel 2010 la macro s'exécute en 20 secondes.

A+

 

[PJstef]

Re : Remplacement de données multiples

Re,

D'une part, merci pour l'info. Je pense que je n'aurai jamais 1000 valeurs cibles, donc jamais plus de 3-4 secondes d'exécution a priori.


D'autre part, j'ai aussi fait des tests, et dans mon cas il s'avère que les valeurs cibles ne sont prises en compte QUE si elles sont au format standard. Néanmoins les valeurs remplacées dans la base de donnée demeurent au format monétaire après remplacement, donc pas de souci de ce coté.

J'ai ajouté au début du code une macro qui met les colonnes CIBLE et REEL en format standard avant de débuter l'analyse principale.

Code:

Sub Remplacer()

Sheets("Feuil2").Select
Columns("A:B").Select
Selection.NumberFormat = "General"

Dim c As Range
Application.ScreenUpdating = False

For Each c In Sheets("Feuil2").[A:A].SpecialCells(xlCellTypeConstants)
Sheets("Feuil1").[E:F].replace c, c(1, 2), xlWhole
Next
End Sub


Voila!

 

[job75]

Re : Remplacement de données multiples

Re,

(...) dans mon cas il s'avère que les valeurs cibles ne sont prises en compte QUE si elles sont au format standard.

Pas besoin de modifier le format en Feuil2, il suffit de rechercher la valeur .Value2 :

Sub Remplacer()
'Feuil1 et Feuil2 sont les CodeNames des feuilles
Dim c As Range
Application.ScreenUpdating = False
For Each c In Feuil2.[A:A].SpecialCells(xlCellTypeConstants)
  Feuil1.[E:F].Replace c.Value2, c(1, 2), xlWhole
Next
End Sub

Bonne nuit.

 

[job75]

Re : Remplacement de données multiples

Bonjour PJstef, le forum,

Une solution par tableaux VBA, plus compliquée mais plus rapide :

Sub RemplacerTableau()
Dim derlig&, P As Range, t1, t2, ub&, i&, v1, v2, j&, v
'---préparation---
With Feuil1 'CodeName
  derlig = .[E:F].Find("*", , , , xlByRows, xlPrevious).Row
  Set P = .Range("E1:F" & derlig)
End With
t1 = P 'matrice, plus rapide
With Feuil2 'CodeName
  t2 = .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
ub = UBound(t2)
'---analyse---
For i = 1 To UBound(t1)
  v1 = t1(i, 1): v2 = t1(i, 2)
  For j = 1 To ub
    v = t2(j, 1)
    If v = v1 Then t1(i, 1) = t2(j, 2)
    If v = v2 Then t1(i, 2) = t2(j, 2)
  Next
Next
'---restitution---
P = t1
End Sub

En testant sur le même fichier qu'au post #5 la durée d'exécution passe à 1,6 seconde 😱

A+

 

[PJstef]

Re : Remplacement de données multiples

Bonjour job75,

En effet ça devient plus compliqué la!
J'avoue ne pas comprendre grand chose au dernier code... 😱
Mais Je vais décortiquer tout ça pour mieux comprendre.!

Merci encore pour tous ces tuyaux, ô combien utiles.

Bonne journée

 

[PJstef]

Re : Remplacement de données multiples

Au fait, j'ai une question de forme à propos du forum (aaahhh les néophytes...) :

comment fait-on pour mettre du texte (généralement un code) dans un cadre indépendant, comme c'est le cas dans les posts #7 et #8 par exemple.?

 

[job75]

Re : Remplacement de données multiples

Re,

Cliquer en bas sur "Aller en mode avancé".

Sélectionner le texte à encadrer.

En haut cliquer sur le trombone (balise CODE).

A+