Discussion :

[Marino69]

Bonjour,

Toute débutante que je suis en VBA, j’avoue que j’ai trouvé dans un livre la formule magique de l’étape 1 de la macro que j’essaie de construire, et tâtonne depuis plusieurs jours pour l’adapter.
L’objectif est de compléter un tableau de suivi de la mise à jour de documents (« SuiviMaJ ») à l’aide d’un rapport hebdomadaire.
Ce rapport liste les documents qui ont changé de statut dans la semaine. Etant envoyé par mail par un outil de Gestion Electronique de Documents, il est copié manuellement dans une feuille du même classeur que le tableau de suivi (feuille « ExploitReport »). Les valeurs « DocNumber » « Statut » et « DateStatut » sont extraites des cellules importées grâce des formules déjà en place dans respectivement les colonnes A,B et C.

Les documents sont identifiés par leur « DocNumber » dans les deux tableaux.

Dans le tableau « SuiviMaJ », la macro doit parcourir toutes les cellules de la colonne des « DocNumber » et compléter les colonnes « Statut » et « DateStatut » avec les valeurs trouvées pour les « DocNumber » présents dans la feuille « « ExploitReport » (pour mémo, ce sont les résultats des formules).
De plus,si les cellules Statut et Date du tableau « SuiviMaJ » sont déjà renseignées, la macro doit passer au « DocNumber » suivant, sans copier les valeurs trouvées (les valeurs initiales ne doivent pas être remplacées).

Le problème : la macro balaye bien tous les « DocNumber » du tableau « SuiviMaJ » et n’écrase pas les valeurs déjà renseignées … mais elle écrit « #VALEUR ! » à la place de « Statut » et « DateStatut ».
Le disfonctionnement est sans doute est à l'étape 1 (cf. code): mauvaise utilisation de la fonction « Find » ou pb lors de la transformation de la cellule trouvée en n° de ligne ?

Je me lance donc pour la première fois à poser une question sur un forum ! Merci d’avance à qui viendra m’aider. Et déjà merci pour toutes les réponses que j’ai déjà trouvées !

Voici le code en question :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
Sub Macro1()
 
Dim Report As Worksheet
Set Report = Worksheets("ExploitReport")
 
Dim DocNumber As Variant
Dim Ligne As Variant
Dim Statut As Variant
Dim DateStatut As Variant
 
Sheets("SuiviMaJ").Range("E2").Select
 
While ActiveCell.Value <> Empty
 
    DocNumber = ActiveCell.Value
 
        If IsEmpty(ActiveCell.Offset(0, 5)) Then
            'Etape 1 - Trouver la cellule de DocNumber dans ExploitReport, et convertir ses coordonnées en ligne puis donner le n° de ligne à la variable Ligne'
            Ligne = [Report.Range("A1:A100").Find(What:=DocNumber, LookIn:=xlFormulas, LookAt:=xlWhole).Address]
            Ligne = [Range(Ligne).Row]
            Ligne = CInt(Ligne)
 
            'Etape 2 - Rechercher les valeurs Satut et DateStatut dans ExploitReport'
            Statut = [Report.Cells(Ligne, 2).Value]
            DateStatut = [Report.Cells(Ligne, 3).Value]
 
            'Etape 3 - copier Statut et date statut dans le tableau SuiviMaJ'
            ActiveCell.Offset(0, 5).Range("A1").Select
            ActiveCell.FormulaR1C1 = Statut
            ActiveCell.Offset(0, 1).Range("A1").Select
            ActiveCell.FormulaR1C1 = DateStatut
 
            ActiveCell.Offset(1, -6).Range("A1").Select
       End If
 
       If Not IsEmpty(ActiveCell.Offset(0, 5)) Then
            ActiveCell.Offset(1, 0).Range("A1").Select
       End If
 
Wend
 
    Set Report = Nothing
 
End Sub

[Docmarti]

Bonjour.

Bel effort mais plusieurs erreurs.

ActiveCell est à oublier. On doit absolument être plus précis sinon le code devient rapidement illisible.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Option Explicit
 
Sub Macro1()
 
Dim Origine As Worksheet, Dest As Worksheet
 
Dim DocNumber As Variant
Dim Ligne As Variant
Dim Statut As Variant
Dim DateStatut As Variant
Dim reponse
Dim i As Long
Dim c As Range, r As Range
 
Set Origine = Worksheets("ExploitReport")
Set Dest = Sheets("SuiviMaJ")
 
Sheets("SuiviMaJ").Activate
Sheets("SuiviMaJ").Range("E2").Select
 
For i = 2 To Rows.Count
 Set c = Dest.Cells(i, 5)
  c.Select ' Ce Select sert uniquement durant la programmation pour s'assurer que le programme est correct
 
 If c.Value = "" Then
  Exit For ' pour sortir de cette routine FOR...NEXT
 
 Else
    DocNumber = c.Value
 
        If c.Offset(0, 5).Value = "" Then
 
         Set r = Origine.Range("A1:A100").Find(What:=DocNumber, LookIn:=xlFormulas, LookAt:=xlWhole)
 
         If r Is Nothing Then
 
          reponse = MsgBox(DocNumber & " non trouve", vbOKCancel, "Erreur")
          If reponse = vbCancel Then Exit Sub
 
         Else
            Ligne = r.Row
 
            'Etape 2 - Rechercher les valeurs Satut et DateStatut dans ExploitReport'
 
            Statut = Origine.Cells(Ligne, 2).Value
 
            DateStatut = Origine.Cells(Ligne, 3).Value
 
            c.Offset(0, 5).Value = Statut
 
            c.Offset(0, 6).FormulaLocal = DateStatut
          End If
 
       End If
 
 End If
Next
 
End Sub

[Marino69]

Bonjour,
Tout d'abord, merci beaucoup pour votre aide.
Je retiens le principe de la boucle « For » en prérequis qui allège la recherche en ciblant sur les DocNumber sans Status, ainsi que la bonne pratique de ne pas utiliser « ActiveCell ».
Voici le résultat obtenu avec la macro corrigée :
Dans le tableau Destinataire (SuiviMaJ), la macro balaye les DocNumber dont le Statut est vide. Cependant, elle renvoie le message « non trouve » pour tous les DocNumber testés, car elle ne reconnait pas le résultat d’une formule . (En effet en remplaçant la formule du tableau Origine (ExploitReport) par la valeur du DocNumber, la macro fonctionne parfaitement).
Mes nouvelles questions :
1 Comment faire en sorte que la fonction Find reconnaisse les résultats des formules du tableau Origine ("ExploitReport")
2 A terme il faudra que je supprimer l’étape du message « non trouvé », car seulement quelques documents seront mis à jour chaque semaine pour un fichier SuiviMaJ comportant 1000 lignes environ.
Pour l’instant, je ne sais pas comment écrire : If r Is Nothing Then « passer au DocNumber suivant »
3 Pour ma compréhension : la variable c a été définie juste après « For » comme étant la 5eme cellule à droite de de la cellule active car je devine que i est, par convention, la ligne de la cellule active.
En l’occurrence la cellule active est celle d’un DocNumber, or, juste après « Else », la valeur de c est attribuée à la variable DocNumber .
Il n'y a pas de nouvelle définition de c dans « Else » et pourtant je constate en faisant fonctionner la macro, que la cellule activée est belle et bien celle du DocNumber : pourriez-vous expliquer pourquoi ? (Je m’attendais à que la cellule activée soit la 5eme à droite du DocNumber, comme definit dans "For").
Merci encore DocMarti - En espérant ne pas abuser de votre temps ...
Cordialement - Marino

[Docmarti]

Mes nouvelles questions :
1 Comment faire en sorte que la fonction Find reconnaisse les résultats des formules du tableau Origine ("ExploitReport")

Il faut remplacer xlFormulas par xlValues s'il y a des valeurs qui sont le résultat de formules. Par contre xlValues n'est pas toujours approprié pour rechercher des nombres ou des dates, surtout si la largeur de la colonne est insuffisante pour permettre l'affichage de tous les chiffres qui composent le nombre.

2 A terme il faudra que je supprimer l’étape du message « non trouvé », car seulement quelques documents seront mis à jour chaque semaine pour un fichier SuiviMaJ comportant 1000 lignes environ.

Vois le code qui suit.

Pour l’instant, je ne sais pas comment écrire : If r Is Nothing Then « passer au DocNumber suivant »
3 Pour ma compréhension : la variable c a été définie juste après « For » comme étant la 5eme cellule à droite de de la cellule active car je devine que i est, par convention, la ligne de la cellule active.
En l’occurrence la cellule active est celle d’un DocNumber, or, juste après « Else », la valeur de c est attribuée à la variable DocNumber .
Il n'y a pas de nouvelle définition de c dans « Else » et pourtant je constate en faisant fonctionner la macro, que la cellule activée est belle et bien celle du DocNumber : pourriez-vous expliquer pourquoi ? (Je m’attendais à que la cellule activée soit la 5eme à droite du DocNumber, comme definit dans "For").

Le code n'utilise aucune cellule active. Ce n'est pas nécessaire. Il copie a tour de rôle chacune des cellules dans l'objet C sans avoir besoin de sélectionner la cellule. C'est d'ailleurs une très mauvaise technique que de sélectionner les cellules. Pour faciliter ta compréhension, le code qui suit n'utilise pas C pour faire référence aux cellules.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Option Explicit ' Pour rendre obligatoire la déclaration de toutes les variables utilisées
 
Sub Macro2()
 
Dim Origine As Worksheet, Dest As Worksheet
 
Dim DocNumber As Variant
Dim Ligne As Variant
Dim Statut As Variant
Dim DateStatut As Variant
Dim reponse
Dim i As Long
Dim R As Range
 
Set Origine = Worksheets("ExploitReport")
Set Dest = Sheets("SuiviMaJ")
 
'Dest.Activate
 
For i = 2 To Rows.Count
 
  DocNumber = Dest.Cells(i, 5).Value
 
  'Dest.Cells(i, 5).Select '  Ce Select sert uniquement durant la programmation pour s'assurer que le programme est correct
                           ' Ensuite on peut supprimer cette commande ou on la fait précéder d'une apostrophe pour la transformeer en commentaire
 
 If DocNumber = "" Then
  Exit For ' pour sortir de cette routine FOR...NEXT
 
 Else
 
        If Dest.Cells(i, 5).Offset(0, 5).Value = "" Then
         'Voici comment utiliser FIND
         Set R = Origine.Range("A1:A100").Find(What:=DocNumber, LookIn:=xlValues, LookAt:=xlWhole) 'xlValues remplace xlFormulas pour chercher également dans les cellules dont la valeur est le résultat d'une formule
          'Le Set R fait que R n'est pas la valeur, mais un objet Range (la cellule elle-même où a été trouvée la valeur recherchée
         If R Is Nothing Then
 
          'reponse = MsgBox(DocNumber & " non trouvé ", vbOKCancel, "Erreur") 'Cette commande est désactivée par l'apostrophe
          'If reponse = vbCancel Then Exit Sub 'Cette commande est désactivée par l'apostrophe
 
         Else
            Ligne = R.Row
 
            'Etape 2 - Rechercher les valeurs Satut et DateStatut dans ExploitReport'
 
            Statut = Origine.Cells(Ligne, 2).Value
 
            DateStatut = Origine.Cells(Ligne, 3).Value
 
            Dest.Cells(i, 5).Offset(0, 5).Value = Statut ' Utiliser Value pour Texte ou Nombre
 
            Dest.Cells(i, 5).Offset(0, 6).FormulaLocal = DateStatut 'Utiliser FormulaLocal pour les Dates
 
          End If
 
       End If
 
 End If
Next
 
End Sub

[Marino69]

Merci beaucoup Docmarti!

La macro fonctionne impeccablement et tes explications sont très claires.
Ton coup de pouce (significatif et pédagogique!) m'encourage à tenter d'autres petits projets.
Alors, sans doute à bientôt...

Cordialement,
Marino