Remplacement dans une colonne à partir d'une autre table

[siocnarf]

Bonjour,

Situation:
J'ai deux feuilles dans un même classeur (feuil1 et feui3)

Je souhaite prendre toutes les valeurs de la colonne 1 de la feuille 3 et effectuer des recherches et remplacement dans la colonne 9 de la feuille 1... à partir des données se trouvant dans la colonne 1 de la feuille 3. Je souhaite remplacer toutes les valeurs exactes que l'on retrouve dans la feuil1 et qui concorde avec celles de la feuil3.

Mais vous devinerez que je n'en viens pas à bout. Où est mon erreur?

Mon erreur est "incompatibilité de type.

nbligne = Sheets(1).Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Rows.Count
ActiveSheet.Range(Cells(2, 9), Cells(nbligne, 9)).Select 'Sélectionne la colonne 9 de la feuille 1

With Selection
  For Each c In Feuil3.Range("A2", Feuil3.Range("A" & Feuil3.Rows.Count).End(xlUp))
    .Columns(c(1, 1)).Replace "*" & c & "*", "#N/A"
  Next

J'ai tenté

c.Replace "*" & c & "*", "#N/A"

Mais cela écrase simplement ma colonne 1 de la feuil3. Dans la feuil3, j'ai 450 lignes et dans la feuil1, j'ai 25 000 lignes.

Merci,

François

 

[Paf]

Re : Remplacement dans une colonne à partir d'une autre table

Bonjour

Je souhaite remplacer toutes les valeurs exactes que l'on retrouve dans la feuil1 et qui concorde avec celles de la feuil3.

Rien compris:
qu'est ce qu'une valeur exacte?
remplacer toutes les valeurs exactes , oui mais par quoi?

Quelques précisions ?

A+

 

[siocnarf]

Re : Remplacement dans une colonne à partir d'une autre table

Bonjour



Rien compris:
qu'est ce qu'une valeur exacte?
remplacer toutes les valeurs exactes , oui mais par quoi?

Quelques précisions ?

A+

C'est tout un défi d'être clair
Je joins un exemple.

Vous remarquerez que dans l'onglet Feuil3 se retrouve les données servant de dictionnaire de mot à retrouver.
Parmi ce dictionnaire se retrouve l'inscription: Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729

Dans l'onglet Inventaire, se retrouve un "inventaire"
Dans la colonne A se retrouve: Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148

Je souhaite donc que mon Replace identifie, tel que démontrer dans mon code plus haut, les
Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729
et non pas les Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148

Je souhaite effectuer cette manipulation pour tout le dictionnaire sur mon inventaire. Un même fichier peut apparaître plus d'un fois.

Merci,

François

 

[Paf]

Re : Remplacement dans une colonne à partir d'une autre table

Re,

si la différence entre feuille3(dictionnaire) et feuille inventaire tient à des caractères supplémentaires en feuille inventaire ( tel que l'exemple le précise), le code ci dessous fonctionne; mais vu le nombre de ligne , le traitement est très long. Il faudrait l'adapter en passant par des tableaux. Fourni pour le principe:

pour chaque ligne de colonne 9, on vérifie s'il existe une valeur approchante dans la feuille 3, si oui on copie la valeur de la feuille 3 dans la cellule de la colonne 9.

Sub Remplacer()
Dim i As Long, j As Long
Dim WD As Worksheet, WI As Worksheet
Set WD = Worksheets("Feuil3") 'feuille dictionnaire
Set WI = Worksheets("Inventaire") 'feuille Inventaire

For i = 1 To WI.Range("A" & Rows.Count).End(xlUp).Row
    For j = 1 To WD.Range("A" & Rows.Count).End(xlUp).Row
        If WI.Cells(i, 9) Like WD.Cells(j, 1) & "*" Then
            WI.Cells(i, 9) = WD.Cells(j, 1)
        End If
    Next
Next
End Sub

A+

 

[siocnarf]

Re : Remplacement dans une colonne à partir d'une autre table

Bonjour,

Ne serait-il pas plus rapide de prendre le dictionnaire et de faire un replace ave N/A pour tous les termes dans l'inventaire qui ont une correspondance avec le dictionnaire? Ainsi plutôt que de prendre les 20 000 lignes et de les comparer avec les 400 ont prend les 400 et on recherche dans les 20 000?

Le but n'est pas d'écrire le dictionnaire dans l'inventaire mais de "marquer" les correspondances dans la colonne 9.

Pourquoi mon code du début ne fonctionne pas?

Merci,

François

 

[Paf]

Re : Remplacement dans une colonne à partir d'une autre table

Re

20000*400 ou 400*20000 il ne doit pas y avoir beaucoup de différence !

pas compris ce que signifie "marquer" les correspondances dans la colonne 9., je croyais que si la colonne 9 contenait au moins la correspondance du dico, il fallait y mettre la valeur du dico.

Pourquoi mon code du début ne fonctionne pas?

ne connaissant pas la syntaxe de replace tel que vous l'utilisez j'ai préféré opter pour une solution plus simple, mais pas forcément adaptée

A+

 

[siocnarf]

Re : Remplacement dans une colonne à partir d'une autre table

Bonjour,

Le but du dictionnaire est d'identifier la présence des termes identiques dans l'inventaire. Les mots sont déjà présent. Une fois identifié, il me faut supprimer les lignes identifiées. Le tout ne doit pas prendre plus de 1,5 minutes.

Merci,

François

 

[Paf]

Re : Remplacement dans une colonne à partir d'une autre table

Re

je crois que je comprend de moins en moins, à chaque post une évolution!

s'il s'agit de supprimer les lignes, cela peut être fait en une seule fois (sans passer par l'étape marquage) encore faut il bien préciser la règle:
ce que je crois avoir compris:
si dans le dictionnaire j'ai Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729, il faut supprimer de l'inventaire les lignes qui en colonne 9 contiennent ,au moins, cette valeur. Par exemple Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148

Mais si toutes les lignes de l'inventaire comportant cette valeur sont supprimées, l'inventaire sera-t-il juste?

la différence entre l'inventaire et le dictionnaire tient elle uniquement à quelques caractères supplémentaires dans l'inventaire ?

A+

 

[siocnarf]

Re : Remplacement dans une colonne à partir d'une autre table

Re

s'il s'agit de supprimer les lignes, cela peut être fait en une seule fois (sans passer par l'étape marquage) encore faut il bien préciser la règle:
ce que je crois avoir compris:
si dans le dictionnaire j'ai Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729, il faut supprimer de l'inventaire les lignes qui en colonne 9 contiennent ,au moins, cette valeur. Par exemple Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148

Mais si toutes les lignes de l'inventaire comportant cette valeur sont supprimées, l'inventaire sera-t-il juste?

la différence entre l'inventaire et le dictionnaire tient elle uniquement à quelques caractères supplémentaires dans l'inventaire ?

A+

En fait, cela pourrait être au moins. Si par exemple, je souhaite supprimer toutes les lignes comportant le terme "Mise à jour de *" (par exemple)
ou cela pourrait être toutes les lignes comportant la valeur exacte Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729

Pour les valeurs telle que "Mise à jour de", je pourrais mettre Mise à jour de* dans le dictionnaire et effectuer la recherche sur ce terme générique. Pour les autres n'ayant pas d'étoile, cela serait une recherche exacte...

Mais comment devrais-je procéder?

Merci,

François

François Racine

 

[siocnarf]

Re : Remplacement dans une colonne à partir d'une autre table

Bonjour,

J'ai finalement réussis à écrire ce bout de code:

Columns(1).Select
Selection.Copy
Columns(7).Select
ActiveSheet.Paste

'With Workbooks(I_StrChiffrierDeBase).Sheets(1).UsedRange
Application.ScreenUpdating = False

nbligne = Sheets(1).Cells(Application.Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range(Cells(2, 7), Cells(nbligne, 7)).Select

With Selection
  For Each c In Feuil3.Range("A1", Feuil3.Range("A" & Feuil3.Rows.Count).End(xlUp))
    Selection.Replace What:= _
        c, Replacement:= _
        0, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    '.Columns(c(1, 1)).Replace "*" & c & "*", "#N/A"
  Next
  
  Application.Goto .Cells(1)
End With

With Workbooks(I_StrChiffrierDeBase).Sheets(1).UsedRange
'.Columns(2) = 0 'colonne H par rapport à ma sélection, il s'agit de la colonne 2
  On Error Resume Next 's'il n'y a pas de valeur d'erreur
  'Intersect(.SpecialCells(xlCellTypeConstants, 16).EntireRow, .Columns(2)) = "#N/A"
  'le tri place les #N/A en bas du tableau (pour accélérer la suppression)
  .Resize(, 1).Sort .Columns(7), xlDescending, Header:=xlYes
  .Columns(7).SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
  .Columns(7).Delete xlToLeft
  Application.Goto .Cells(1)
End With

Application.ScreenUpdating = True

End Sub

Ce n'est certes probablement pas optimal. L'exécution complète prend 198 secondes. C'est un peu long. Croyez-vous que l'on puisse accélérer le traitement?

Merci,

François

 

[Paf]

Re : Remplacement dans une colonne à partir d'une autre table

re bonjour

ci joint un essai permettant de
choisir un seul élément du dictionnaire avec possibilité de recherche exacte ou non
ou
de lancer le traitement pour l'ensemble du dico avec possibilité de recherche exacte ou non

La suppression réelle des lignes n'est pas activée, à voir dans Private Sub Collectif() et Private Sub Unique() de l'USF pour retirer le commentaire de la ligne de suppression

Un seul souci, les lignes d'inventaire 9,02 ne sont pas supprimées en mode dico entier ( ??? j'y travaille)

Pour lancer cliquer sur le bouton CommandButton1 de la feuille Inventaire.
Le traitement complet demande environ 80 secondes

Vous dites
A+

 

[siocnarf]

Re : Remplacement dans une colonne à partir d'une autre table

Bonjour,

C'est effectivement intéressant. Mais où est le code?? Je n'ai pas trouvé la macro.

Je l'ai finalement trouvé. Ce n'était pas évident pour moi Je vais regarder tout cela.

Je comprend que c'est ce bout de code qui m'intéresse puisque celui-ci pourra être greffé dans mon code complet et qu'il gère le dictionnaire complet. Intéressant...

Mes questions sont en gras dans le code.

Private Sub Collectif()
Dim i As Long, j As Long, DerLig As Long, Etend As String, Plage As String
Dim WI As Worksheet, WD As Worksheet
Set WI = Worksheets("Inventaire") 'feuille Inventaire
Set WD = Worksheets("Feuil3") 'feuille dico
Application.ScreenUpdating = False
'met la feuille source en mode filtre si elle ne l'est pas

if Not WI.AutoFilterMode Then WI.Range("A1:G1").AutoFilter[B]-->Pourquoi Autofilter?[/B]
If CheckBox1 = True Then Etend = "*"

For i = 1 To WD.Range("A" & Rows.Count).End(xlUp).Row
    'tri selon  les critères
    WI.Range("A1").AutoFilter Field:=1, Criteria1:=WD.Cells(i, 1) & Etend[B]-->Que signifie ce bout de code? Qu'Est-ce que "Etend?
[/B]    If WI.[subtotal(3,A:A)-1] > 0 Then 'si on a un résultat[B]--> Je ne comprends pas If WI.[subtotal(3,A:A)-1]
[/B]        'détermination de la dernière ligne du tableau
        DerLig = WI.Range("A" & Rows.Count).End(xlUp).Row
        'DerLig = Plage.SpecialCells(xlVisible).Columns(5).Find("*", , , , xlByColumns, xlPrevious).Row
        'supprime les lignes visibles
        Plage = "2:" & DerLig[B]--> Je ne crois pas avoir remarqué de variable Plage?
[/B]        WI.Rows(Plage).SpecialCells(xlCellTypeVisible).Select ' ligne a supprimer pour la suppression réelle
        'WI.Rows(Plage).SpecialCells(xlCellTypeVisible).Delete ' ligne a activer pour la suppression réelle
        k = k + 1[B]--> Que viens faire cette variable?
[/B]        'MsgBox "en cours :" & WD.Cells(i, 1) & Etend & "  " & [subtotal(3,A:A)-1]
    End If
Next
Application.ScreenUpdating = True
End Sub

Merci,

François

 

[Paf]

Re : Remplacement dans une colonne à partir d'une autre table

Re bonjour

pour la comparaison/suppression j'utilise autofilter c'est la même chose que l'utilisation du filtre automatique sur tableau Excel. Ce qui permet de ne laisser visible que les lignes correspondant au critère souhaité, en l'occurrence chaque article du dico.
S'il n'y a pas d'affichage on passe au suivant . s'il y a affichage on supprime ces lignes.

C'est plus rapide que de balayer ligne par ligne tout l'inventaire pour chaque article du dico. environ 80s pour le test sur le classeur joint avec mon vieux PC.

WI.Range("A1").AutoFilter Field:=1, Criteria1:=WD.Cells(i, 1) & Etend
on filtre sur la colonne 1 selon le critère : contenu de la cellule de la feuille dico. Etend vaut "*" si l'on a coché dans l'USF et rien si on n'a pas coché.

Par exemple pour l'article Connect du dico:
si on ne coche pas sur l'USF on recherchera strictement Connect dans l'inventaire et supprimera la ligne Connect de l'inventaire.
Si l'on a coché dans l'USF on recherchera Connect* dans l'inventaire et supprimera la ligne Connect et les deux lignes Connector ID de l'inventaire.



WI.[subtotal(3,A:A)-1]
donne le nombre de ligne non masquées donc on ne continu que si on a des lignes à traiter

Plage = ...
c'est une variable déclarée en première ligne de déclaration des variables de Private Sub Collectif(); qui n'est pas nécessaire, on aurait pu écrire directement :WI.Rows("2:" & DerLig).....

k = k + 1
pour chaque ligne traitée de l'inventaire on incrémente de 1 une variable qui permet en fin de traitement d'afficher le nombre de lignes supprimées.

Par ailleurs les lignes vides de l'inventaire pertubent le tri et risquent de fausser le traitement. A supprimer donc


A+