Problème macro et amélioration

[Sandra0104]

Bonjour,
Je viens à vous car j'ai besoin d'un coup de main. Je vends des vêtements et je reçois plusieurs fois par an les fichiers de mes fournisseurs (fichiers de 1000 à 4000 lignes environ). Ces fichiers contiennent les produits existants + les nouveaux produits - les produits arrêtés.
J'ai récupéré une macro qui me permettait de mettre à jour le stock et le prix de mon catalogue. Mais ça ne fonctionne pas sur des fichiers de 3500 lignes, la même cellule est répétée.
Bref, ce que je souhaiterai c'est d'avoir dans un fichier :
- 1er onglet : moncatalogue
- 2ème onglet : fichierfournisseur
et une macro qui me permet d'inscrire dans moncatalogue les informations contenues dans fichierfournisseur (prix,stock,description...) des références existantes, mettre en rouge les lignes des produits arrêtés et ajouter en bleu les nouveaux produits.

Je vous remercie par avance de l'aide que vous m'apporterez.

 

[zeb]

Bonjour Sandra,

Nous nous entraidons entre développeurs. Nous acceptons volontiers les débutants en programmation. Mais nous ne réalisons pas de travaux à la demande.

Peut-on t'aider dans ce cadre ?

 

[Sandra0104]

Bonjour Zeb,
Merci de ta réponse.
Voilà le code qui ne fonctionne pas quand le fichier est trop long et qui répète la même cellule.

Sub 
    Dim i As Long, j As Long
    i = 2
    j = 2
    For i = 2 To 3574
        For j = 2 To 1954
            ' // (la référence produit est le point commun aux 2 fichiers)
            If Worksheets("moncatalogue").Range("R" & i).Value Like "*" & _
               Worksheets("fichierfournisseur").Range("B" & j).Value & "*"  Then
                ' // (mise à jour tarif)
                Worksheets("moncatalogue").Range("J" & i).Value = Worksheets("fichierfournisseur").Range("I" & j).Value 
                ' // (mise à jour stock)
                Worksheets("moncatalogue").Range("K" & i).Value = Worksheets("fichierfournisseur").Range("F" & j).Value 
            End If
        Next
        j = 2
    Next
End Sub

Je n'arrive pas à comprendre pourquoi la macro bug.
Pour mettre en couleur, je viens de trouver (il faudra que je teste).
Il me reste à afficher les nouvelles références dans moncatalogue. Je continue de chercher mais j'apprécierai si tu pouvais au fur et à mesure me valider les modifications.
Est-ce possible ?

 

[zeb]

Sandra [:zeb:4] Il faut lire et appliquer le règlement ! Je le fais pour toi cette fois-ci.

 

[Storos]

[strike]On pourra certainement t'aider, mais pour cela, il va falloir respecter les règles de la rubrique...[/strike]

[strike]Je te conseille donc d'utiliser les balises

 autour de ton code. Cela le rendra plus lisible.[/strike] ;)

EDIT: trop tard! zeb a été plus rapide que moi! :D

 

[zeb]

Si, je me permets, ce ne sont pas les fichiers de 3500 lignes mais de 3574 lignes qui posent problèmes. N'est-ce pas ?

Du coup, en lisant la lignes 5 (oh, des numéros de lignes. Tiens, c'est donc pour ça que le modo est si chiant avec ces histoires de règlement, de publication, de balise

, etc), on se dit que la solution est triviale.

 

[zeb]

@storos : oui et en +, j'ai corrigé moi-même. Quoi, il y a deux poids deux mesures ? Oui. Et alors ? :fille:

 

[zeb]

Bon Sandra, si en plus tu veux nous préciser ce que t'as trouvé pour la couleur, on peut t'aider à tout mettre dans la même macro !

 

[Storos]

@zeb: C'est vraiment trop injuste! :calimero:

Et je confirme que la solution est triviale...

Pour la même raison, j'aurais tendance à me poser aussi des questions sur la ligne 6...

 

[zeb]

Pareil !

J'attends de Sandra plus de matière à traiter pour lui proposer une solution plus globale.

 

[Sandra0104]

Merci Zeb, Merci Storos... J'avais utiliser "réponse rapide" donc pas d'insertion code.
Je ne le ferai plus ... promis. Ne vous prenez pas le bec (ou le groin , suis novice et c'est de ma faute. Je ne suis que polie et courtoise (du moins je l'espère!).

Quel est le problème sur les lignes 5 et 6 ?
Avec des fichiers plus légers, la macro ne bug pas.

Pour les références arrêtées avec coloration de la ligne :


[cpp]Else if // A étant la colonne où est noté STOP pour les références arrêtées
Worksheets("moncatalogue").Range("R" & i).Value Like "*" & Worksheets("fichierfournisseur").Range("B" & j).Value & "*" & Worksheets("fichierfournisseur").Range("A" & j).Value & "STOP"
Then
Worksheets("moncatalogue").Rows.Interior.ColorIndex=2[/cpp]

Maintenant il me reste les nouvelles références à incrémenter dans moncatalogue.

Je fais des progrès là, non ?

 

[zeb]

Pas mal pour la débutante que tu prétends être !
Je n'aime pas l'idée que tu n'aies pas compris le problème des lignes 5 et 6.
La boucle For de la ligne 5 permet de lire ton fichier "moncatalogue" de la ligne 2 à la ligne 3574, pas plus !
La boucle For de la ligne 6 permet de lire ton fichier "fichierfournisseur" de la ligne 2 à la ligne 1954, pas plus !

Bon je réfléchis à une solution..... (patience)

 

[Sandra0104]

Je les ai comprises. J'ai indiqué en ligne 5 et 6 le nombre de lignes de mes 2 fichiers, il ne faut pas ?
Tu es en train de me dire que je lis et applique des informations fausses ???? grrrr le net !
Mon code est bon pour la couleur et les références arrêtées ????? (suis pas si blonde alors ! Je ne rêve pas, il doit forcément comporter des erreurs, je n'ai aucune connaissance en macro.

 

[Sandra0104]

Je continue à réfléchir... sur les nouvelles références :

[cpp]Else If Worksheets("fichierfournisseur" ).Range("B" & i).Value Like "*" & Worksheets("moncatalogue" ).Range("R" & j).Value=False
Then
i = i+1
Worksheets("moncatalogue" ).Rows.Interior.ColorIndex=5
Worksheets("moncatalogue" ).Range("R" & i).Value = Worksheets("fichierfournisseur" ).Range("B" & j).Value
Worksheets("moncatalogue" ).Range("J" & i).Value = Worksheets("fichierfournisseur" ).Range("I" & j).Value
Worksheets("moncatalogue" ).Range("K" & i).Value = Worksheets("fichierfournisseur" ).Range("F" & j).Value[/cpp]

Je n'ai indiqué que la copie de 3 colonnes mais j'ajouterai la copie des autres colonnes dont j'ai besoin. Ce n'est pas compliqué, enfin je crois.

 

[zeb]

Sub Macro_a_etudier()
    ' // Le classeur Catalogue
    Dim wk_catalog As Workbook
    Set wk_catalog = ActiveWorkbook
    
    ' // Ouverture du classeur fournisseur
    ' // Copie en dernier onglet de la feuille fournisseur dans le classeur Catalogue
    Dim wk_fournis As Workbook
    Dim n As Integer
    
    ' // --> Ouvertuyre du classeur
    Set wk_fournis = Workbooks.Open("classeur_fournisseur.xls")
    ' // --> Combien d'onglet dans catalogue ?
    n = wk_catalog.Worksheets.Count
    ' // --> On copie le premier onglet de fournisseur après le dernier onglet (n) de catalogue
    wk_fournis.Worksheets(1).Copy , wk_catalog.Worksheets(n)
    wk_fournis.Close ' // on ferme !
    
    ' // --> La page des nouvelles fournitures, c'est dernière du catalogue
    Dim ws_fournis As Worksheet
    Set ws_fournis = wk_catalog.Worksheets(n + 1) ' // (il y en a une de plus)
    ws_fournis.Name = "fichierfournisseur"
    
    ' // --> La page du catalogue, c'est celui qui s'appelle "moncatalogue"
    Dim ws_catalog As Worksheet
    Set ws_catalog = wk_catalog.Worksheets("moncatalogue")
    
    ' // Combien de lignes dans les fournitures ?
    ' // --> On va dire que c'est dans la colonne 1 qu'il y a les références.
    ' // --> On se met dans la dernière ligne de la colonne 1 et on remonte jusqu'à ce qu'on trouve quelque chose.
    Dim zn_fournis As Range
    Set zn_fournis = Range(ws_fournis.Cells(2, 1), ws_fournis.Cells(65536, 1).End(xlUp))
    MsgBox "La zone utile des nouvelles fournitures est " & zn_fournis.Address(False, False)
    
    ' // Combien de lignes dans le catalogue ?
    ' // --> On va dire que c'est dans la colonne 1 qu'il y a les références.
    ' // --> On se met dans la dernière ligne de la colonne 1 et on remonte jusqu'à ce qu'on trouve quelque chose.
    Dim zn_catalog As Range
    Dim dr_catalog As Range ' Pour mémoriser la der de la zone
    Set dr_catalog = ws_catalog.Cells(65536, 1).End(xlUp)
    Set zn_catalog = Range(ws_catalog.Cells(2, 1), dr_catalog)
    MsgBox "La zone utile du catalogue actuel est " & zn_catalog.Address(False, False)
            
    ' // --> On boucle sur les nouvelles fournitures
    Dim cl_fournis As Range ' // cl = cellule
    Dim cl_catalog As Range
    Dim trouve As Boolean
    For Each cl_fournis In zn_fournis
        ' // Pas encore trouvé
        trouve = False
        ' // --> On boucle sur le catalogue actuel
        For Each cl_catalog In zn_catalog
            If cl_fournis.Value = cl_catalog.Value Then
                ' // Trouvée
                trouve = True
                ' Est-ce à mettre à jour ou à supprimer ?
                ' // --> On va dire que c'est dans la colonne 2
                If cl_fournis.Offset(0, 1).Value = "On arrête" Then
                    cl_catalog.EntireRow.Interior.ColorIndex = 3 ' // Rouge
                    ' // Sans doute d'autres choses à faire ici
                Else
                    ' // On recopie tout de fournis --> catalog
                    cl_fournis.EntireRow.Copy cr_catalog.EntireRow
                End If
            End If
        Next
        If Not trouve Then
            ' C'est donc nouveau
            ' // Où copier cette nouvelle ligne ?
            ' // Ben dans dr_catalog + 1 pardi !
            Set dr_catalog = dr_catalog.Offset(1, 0)
            cl_fournis.EntireRow.Copy dr_catalog.EntireRow
            dr_catalog.EntireRow.Interior.ColorIndex = 5 ' // Bleu
        End If
    Next
End Sub

Tu remarqueras le nom subtil de la macro, qui me permet de continuer à dire que :

Nous nous entraidons entre développeurs. Nous acceptons volontiers les débutants en programmation. Mais nous ne réalisons pas de travaux à la demande.

 

[Sandra0104]

Wouahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh !!!
On est loin de ce sur quoi j'étais partie !!! Voilà une soirée bien remplie en perspective (à mon avis, le week-end aussi !)
Tout d'abord Merci Zeb. Ensuite, je vais essayer de faire honneur à ton travail et m'occuper de ce pas de cette macro. Et enfin, je reviendrai vers toi pour te donner le résultat (et te remercier à nouveau!).

 

[Sandra0104]

Et me revoilà !

Suite à "mon étude" à chaud, j'ai quelques questions :

- Quel intérêt d'avoir un classeur classeur_fournisseur.xls ? Il me semble plus simple de copier mon catalogue dans l'onglet moncatalogue et le nouveau fichier de mes fournisseurs dans l'onglet fichierfournisseur. La question se pose quand même, il y a peut être une subtilité que je n'ai pas saisi ! Sachant qu'il faudra que je modifie l'organisation des fichiers des fournisseurs pour qu'il colle à celle de moncatalogue...

- Comment puis-je remplir la colonne 2 avec la valeur "on s'arrête" puisque je ne sais pas quels produits sont arrêtés ? Je me suis mal exprimée. Dans les fichiers fournisseur, on trouve les références en cours (anciennes et nouvelles) et les produits arrêtés n'apparaissent plus. Ne serait-il pas judicieux d'analyser les références dans moncatalogue qui sont absentes dans fichierfournisseur ?

- Dans les lignes des nouveaux produits ajoutés à moncatalogue, pourquoi certaines lignes ne sont pas en bleu ?

- J'ai fait un test en enregistrant une référence supplémentaire dans classeur_fournisseur.xls en ligne 1 puis dans l'ordre des références et cette nouvelle référence n'est pas pris en compte, elle n'apparaît pas en bleu, pourquoi ?

- Pour la mise à jour d'information (stock, prix...), est-ce bien après la ligne 56 que je dois codifier ?

Je me repencherai sur "mon étude" ce week-end, à froid ! Je te ferai part peut-être d'autres questions. Je veux bien appliquer la macro mais je veux surtout comprendre et il y a quelques lignes que je ne comprends pas. Certaines logiques de programmation m'échappent complètement.

 

[zeb]

Pleins de bonnes questions

Pourquoi un classeur pour le fournisseur ?
Ben parce que c'est toi qui a dit en recevoir un. Le programme proposé fait lui-même la copie.

Code succint :

' // --> Ouvertuyre du classeur
Set wk_fournis = Workbooks.Open("classeur_fournisseur.xls" )

Code élaboré :

' // --> Ouverture du classeur
Dim nm_fournis As String
nm_fournis = Application.GetOpenFilename("Classeurs Excel,*.xls,Tous les fichiers (*.*),*.*", 1, "Classeurs fournisseurs", , False)
If Not FileExists(nm_fournis) Then Exit Sub

Set wk_fournis = Workbooks.Open(nm_fournis)

NB: La fonction FileExists n'existe pas. Il faut la créer (ou la

Vous devez être connecté pour voir les liens.

)

Ah, nous n'avons pas d'information de suppression. Si tu n'es pas mal exprimée, c'est que j'ai mal compris... Bizarre... :heink: ... [atch]
Donc, de la ligne 54 à la 64, on vire tout et on écrit :

If cl_fournis.Value = cl_catalog.Value Then
    ' // Trouvé ! On recopie tout de fournis --> catalog
    trouve = True
    cl_fournis.EntireRow.Copy cl_catalog.EntireRow
    Exit For
End If

Pour l'instant, on a parcourut zn_fournis [ligne 48], puis zn_catalog [52] pour trouver [55]. Si on ne trouve pas [67], on a fait certaines choses.

Par contre, on recherche maintenant les lignes de ton catalogue qui ne sont pas présentes dans celui du fournisseur.
Et si je te disait que c'est l'inverse de ce qu'on a déjà fait ?

Donc on ajoute à la fin ceci : on a parcourut zn_catalog, puis zn_fournis pour trouver. Si on ne trouve pas, on a fait certaines choses (mettre en rouge).
Donne-nous ce petit bout de code qu'on voit comment tu te débrouilles !

- Dans les lignes des nouveaux produits ajoutés à moncatalogue, pourquoi certaines lignes ne sont pas en bleu ?
- J'ai fait un test en enregistrant une référence supplémentaire dans classeur_fournisseur.xls en ligne 1 puis dans l'ordre des références et cette nouvelle référence n'est pas pris en compte, elle n'apparaît pas en bleu, pourquoi ?

Que nous dit le message de la ligne 33 ? Alors pourquoi mets-tu des trucs en ligne 1, puisqu'il ne vont pas être pris en compte ?
Essaie de voir comment ligne 32, je fais expres de ne pas prendre la ligne 1 en compte.

- Pour la mise à jour d'information (stock, prix...), est-ce bien après la ligne 56 que je dois codifier ?

Euh, non. Regarde le commentaire de la ligne 62.

 

[Sandra0104]

Bonjour,

Je reçois un catalogue par fournisseur(j'en ai plusieurs) 2 à 4 fois par an. Les catalogues ne se présentent pas tous de la même manière. Certains peuvent avoir plusieurs onglets (prix, stock, description.....). Dans mon esprit, j'étais partie pour copier les colonnes qui m'intéressaient dans l'onglet fichierfournisseur. Voire même de mettre à la suite les différents fournisseurs pour traiter le catalogue en une seule fois (les fournisseurs n'ont pas les même type de références). Je n'avais pas songé un instant à l'ouverture et la copie automatisées mais pourquoi pas. Ce qui me chagrine, c'est mon côté feignante, c'est la manipulation des fichiers fournisseurs pour les mettre à l'identique de mon catalogue qui compte 35 colonnes !!!! Quelle est la meilleur méthode, je ne sais pas !

Il est évident que si je ne donne pas toutes les informations et toutes mes réflexions, la communication va être difficile.

Par contre, on recherche maintenant les lignes de ton catalogue qui ne sont pas présentes dans celui du fournisseur.
Et si je te disait que c'est l'inverse de ce qu'on a déjà fait ?


Donc on ajoute à la fin ceci : on a parcourut zn_catalog, puis zn_fournis pour trouver. Si on ne trouve pas, on a fait certaines choses (mettre en rouge).

Je suppose qu'en ayant déjà fait le rapprochement entre zn_fournis et zn_catalog des références identiques, je n'ai pas à refaire la même chose, n'est-ce-pas ??? (vraiment la logique informatique me perturbe !)

[cpp] For Each cl_catalog In zn_catalog
If cl_fournis.Value = cl_catalog.Value Then
trouve = False
cl_catalog.EntireRow.Interior.ColorIndex = 3 ' // Rouge
End If
[/cpp]

Pour la ligne 62, j'avais supposé supprimer les lignes 56 à 63 puisque la colonne 2 n'existait pas !

Quant à la référence ajoutée en ligne 1, toutes mes excuses, je m'exprime mal, la ligne 1 est en faite la ligne 2, je n'avais pas pris en compte la 1ère ligne d'intitulé des colonnes (en faite 1ère ligne des références produits) grrrrr !
Cela n'empêche pas le fait qu'elle ne soit pas pris en compte même si j'insère la référence 50 entre la 49 et la 51 !!!!??

 

[zeb]

Abandonnons l'idée d'ouvrir les catalogues automatiquement. Ce serait trop investir en réglage minutieux pour reproduire une action somme toute banale 4 fois par an.

Regarde mieux la ligne 61, il y a un else (sinon). Donc, comme proposé, il faut garder cette ligne. Et elle qui ajoute les nouvelles références.

Par commodité, je recopie toute la ligne (EntireRow), mais tu peux ne copier que certaines colonnes :

If cl_fournis.Value = cl_catalog.Value Then
    ' // Trouvé ! On recopie tout de fournis --> catalog
    trouve = True
    ' // cl_fournis.EntireRow.Copy cl_catalog.EntireRow
    
    cl_catalog.Offset(0, 2).Value = cl_fournis.Offset(0, 2).Value
    cl_catalog.Offset(0, 3).Value = cl_fournis.Offset(0, 4).Value
    cl_catalog.Offset(0, 3).Value = cl_fournis.Offset(0, 4).Value
    ...
    Exit For
End If

Une petite erreur s'est glissé dans mon code, ligne 63 : la destination, c'est cl_catalog, pas dr_catalog. Toutes les confuses.

Je suppose qu'en ayant déjà fait le rapprochement entre zn_fournis et zn_catalog des références identiques, je n'ai pas à refaire la même chose, n'est-ce-pas ??? (vraiment la logique informatique me perturbe !)

For Each cl_catalog In zn_catalog
    If cl_fournis.Value = cl_catalog.Value Then
        trouve = False
        cl_catalog.EntireRow.Interior.ColorIndex = 3 ' // Rouge
    End If
Next

:/ Il manque un bout, là ! Et pis ce n'est pas parce que tu n'as pas encore trouvé que tu ne vas pas le faire. Etudie mieux ton bout de code.
Les deux zones à scruter bien les mêmes. Le fournisseur n'a pas changé. Et pas la peine de scruter les nouvelles réfs de ton catalogue !

Regarde, et dis-moi que ça te paraît évidement maintenant :

For Each cl_catalog In zn_catalog
    trouve = False
    For Each cl_fournis In zn_fournis
        If cl_fournis.Value = cl_catalog.Value Then
            trouve = True
            Exit For
        End If
    Next
    If Not trouve Then
        cl_catalog.EntireRow.Interior.ColorIndex = 3 ' // Rouge
    End If
Next