VBA Référence de cellule/Référence structurée

**gdmeunier** · 29/03/2022, 11h03

Bonjour,
Ma question concerne la syntaxe en VBA lorsqu’on travaille avec une formule en référence de cellule ou en référence structurée.
Je mets à jour périodiquement une feuille (Format Tableau) avec une colonne contenant Oui ou vide suivant une formule qui gère une différence de date. J’ajoute la feuille de mise à jour en entier à la base de données (Format Tableau) puis je copie la colonne de la base de données contenant la date et la recopie en valeur (éliminer la formule) dans la base de données finale (Tableau).
Le code est le suivant :

Code :

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

1
2
Range("G:G").Copy
Range("G:G").PasteSpecial Paste:=xlPasteValues

Si ma formule est en référence structurée =SI([@[DATE_FCM]]>[@DATE];"Oui";""), j’obtiens #VALEUR ! dans la base de données finale
Si cette formule est en référence de cellule =SI(H8>F8;"Oui";"") ça fonctionne et j'obtiens Oui ou vide.

J’aimerais comprendre pourquoi cela fonctionne différemment. Le format structuré doit modifier quelque chose lors de la recopie. J'aimerais aussi avoir une indication pour la syntaxe me permettant d’utiliser, quand même, une référence structurée.

Le code complet ci-dessous

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
Sub FCM_MàJ()
'Ajout de la feuille FCM_Mise à Jour à FCM_data
Dim ligne As Long
Dim Rep As Integer
Rep = MsgBox("Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE")
' Réponse OK
If Rep = 1 Then
    'Tableau de destination vide
        If Range("Ta_FCM_Data").ListObject.DataBodyRange Is Nothing Then
             'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
             Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(5, 1)
             'Affecter le numéro 1 à la 1° ligne
             Sheets("FCM_Data").Range("A5").Value = 1
             'Copier-coller la formule date en valeur
             Sheets("FCM_Data").Select
             Range("F:F").Copy
             Range("F:F").PasteSpecial Paste:=xlPasteValues
            'Copier-coller le Oui en valeur
             Range("G:G").Copy
             Range("G:G").PasteSpecial Paste:=xlPasteValues
            'Déselectionner la colonne F:F
             Application.DisplayAlerts = False
             ' Supprimer MFC
             Columns("H:H").Select
             Cells.FormatConditions.Delete
 
    'Tableau de destination déjà rempli
        Else
            'Numéro de la première ligne vide de la base de données
            ligne = Sheets("FCM_Data").Range("A1048576").End(xlUp).Row + 1
            'Confirmation et copie de la mise à jour
'          MsgBox "Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE"
            'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
            Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(ligne, 1)
            'Copier-coller la formule Date en valeur
            Sheets("FCM_Data").Select
            Range("F:F").Copy
            Range("F:F").PasteSpecial Paste:=xlPasteValues
            'Copier-coller le Oui en valeur
             Range("G:G").Copy
             Range("G:G").PasteSpecial Paste:=xlPasteValues
            'Déselectionner la colonne F:F
            Application.DisplayAlerts = False
            'Supprimer MFC
            Columns("H:H").Select
            Cells.FormatConditions.Delete
        End If
Else
    Exit Sub
End If
    'Compléter la numérotation
     Call FCM_NumAuto
     'Supprimer les lignes vides
     Call FCM_SuppressionLignes
    'Remettre à blanc la colonne H
    Call FCM_MàJBlanc
End Sub

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
Sub FCM_NumAuto()
'Repart de la valeur la plus élevée quelque soit l'ordre de tri
  Dim i As Long, Maxi As Long
  With Sheets("FCM_Data").ListObjects("Ta_FCM_Data")
    Maxi = Application.Max(.ListColumns(1).DataBodyRange)
    For i = 1 To .ListRows.Count
      If .ListRows(i).Range(1) = "" Then
        Maxi = Maxi + 1
        .ListRows(i).Range(1) = Maxi
      End If
    Next i
  End With
End Sub
Sub FCM_SuppressionLignes()
'Suppression des lignes vides
Dim i As Integer
Range("Ta_FCM_Data").Activate
For i = Selection.Cells(Selection.Cells.Count).Row _
To Selection.Cells(1).Row Step -1
   If Cells(i, "H").Value = "" Or _
      IsEmpty(Cells(i, "H").Value) Then Rows(i).Delete
Next i
End Sub
Sub FCM_MàJBlanc()
'Mise à blanc des colonnes H
Sheets("FCM_MàJ").Select
              Range("H8:H43").ClearContents
End Sub

**21Formatic** · 30/03/2022, 09h41

Bonjour,

J'imagine que ta macro pourrait devenir ceci :

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
Sub FCM_MàJ()
'Ajout de la feuille FCM_Mise à Jour à FCM_data
Dim ligne As Long
Dim Rep As Integer
Rep = MsgBox("Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE")
' Réponse OK
If Rep = 1 Then
    'Tableau de destination vide
        If Range("Ta_FCM_Data").ListObject.DataBodyRange Is Nothing Then
             'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
             Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(5, 1)
             'Affecter le numéro 1 à la 1° ligne
             Sheets("FCM_Data").Range("A5").Value = 1
             'Copier-coller la formule date en valeur
 
    'Tableau de destination déjà rempli
        Else
            'Numéro de la première ligne vide de la base de données
            ligne = Sheets("FCM_Data").Range("A1048576").End(xlUp).Row + 1
            'Confirmation et copie de la mise à jour
'          MsgBox "Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE"
            'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
            Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(ligne, 1)
            'Copier-coller la formule Date en valeur
        End If
 
With Sheets("FCM_Data")
    .Range("F:G").Value = .Range("F:G").Value
    .Columns("H:H").Cells.FormatConditions.Delete
End With
Else
Exit Sub
End If
'Compléter la numérotation
     Call FCM_NumAuto
     'Supprimer les lignes vides
     Call FCM_SuppressionLignes
    'Remettre à blanc la colonne H
    Call FCM_MàJBlanc
End Sub

Quasiment tous les .select sont inutiles, évitables et ralentissent ton programme.
Range("F:G").Value = Range("F:G").Value correspond à un copier coller valeurs uniquement. Cela fonctionne sur des tableaux structurés. Et tu n'as pas besoin de le faire colonne par colonne comme tu le faisais avant. Pas plus que de sélectionner la feuille avant.

Je ne peux pas tester tout ceci n'ayant pas ton fichier. Je te laisse nous redire si ça coince.

**gdmeunier** · 30/03/2022, 16h42

21formatic bonjour et merci!
Je suis un utilisateur débutant du VBA (Suivi-Evaluation ) mais je progresse! Le code me sert à copier une table de mise à jour périodique dans une base qui est, ensuite, traitée (TCD, etc.).
Le ligne ci-dessous du code proposé, en fait, crée 1 048 576 lignes, c'est très long et ça plante plus loin.

Code :

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

1
2
With Sheets("FCM_Data")
    .Range("F:G").Value = .Range("F:G").Value

Par ailleurs, je recopie une colonne avec une référence à une cellule $I$4 que je n'arrive à copier en valeur, la formule reste active dans la base finale et me renvoie une autre valeur. Comment m'assurer que je ne copie que la valeur et non la formule? Le code xlPasteValue fonctionne si j'ai des formules (=SI ...) mais pas avec cette référence à une cellule.
Ne serait pas possible et plus simple de copier la table Ta_FCM_MàJ entièrement en valeur, à la suite de la table Ta_FCM_Data, donc plus besoin de copier-coller en valeur.
Ci dessous-code le code initial et le code proposé. J'ai fait 2 modifications : mis la suppression des lignes vides avant la numérotation et activer la fenêtre de la base eb fin de mise à jour.
J'ai attaché le classeur si besoin.

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
Sub FCM_MàJ()
'Version Developpez
'Ajout de la feuille FCM_Mise à Jour à FCM_data
Dim ligne As Long
Dim Rep As Integer
Rep = MsgBox("Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE")
' Réponse OK
If Rep = 1 Then
    'Tableau de destination vide
        If Range("Ta_FCM_Data").ListObject.DataBodyRange Is Nothing Then
             'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
             Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(5, 1)
             'Affecter le numéro 1 à la 1° ligne
             Sheets("FCM_Data").Range("A5").Value = 1
             'Copier-coller la formule date en valeur
 
    'Tableau de destination déjà rempli
        Else
            'Numéro de la première ligne vide de la base de données
            ligne = Sheets("FCM_Data").Range("A1048576").End(xlUp).Row + 1
            'Confirmation et copie de la mise à jour
'          MsgBox "Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE"
            'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
            Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(ligne, 1)
            'Copier-coller la formule Date en valeur
        End If
 
With Sheets("FCM_Data")
    .Range("F:G").Value = .Range("F:G").Value
    .Columns("H:H").Cells.FormatConditions.Delete
End With
Else
Exit Sub
End If
'Compléter la numérotation
     Call FCM_NumAuto
     'Supprimer les lignes vides
     Call FCM_SuppressionLignes
    'Remettre à blanc la colonne H
    Call FCM_MàJBlanc
End Sub

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
62
63
64
65
66
67
Sub FCM_MàJOld()
'Version initiale GM
'Ajout de la feuille FCM_Mise à Jour à FCM_data
Dim ligne As Long
Dim Rep As Integer
Rep = MsgBox("Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE")
' Réponse OK
If Rep = 1 Then
    'Tableau de destination vide
        If Range("Ta_FCM_Data").ListObject.DataBodyRange Is Nothing Then
             'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
             Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(5, 1)
             'Affecter le numéro 1 à la 1° ligne
             Sheets("FCM_Data").Range("A5").Value = 1
             'Copier-coller la formule date en valeur
             Sheets("FCM_Data").Select
             Range("F:F").Copy
             Range("F:F").PasteSpecial Paste:=xlPasteValues
            'Copier-coller le FCM en valeur
             Range("G:G").Copy
             Range("G:G").PasteSpecial Paste:=xlPasteValues
            'Copier-coller le Statut en valeur
             Range("H:H").Copy
             Range("H:H").PasteSpecial Paste:=xlPasteValues
            'Déselectionner la colonne F:F
             Application.DisplayAlerts = False
             ' Supprimer MFC
             Columns("I:I").Select
             Cells.FormatConditions.Delete
 
    'Tableau de destination déjà rempli
        Else
            'Numéro de la première ligne vide de la base de données
            ligne = Sheets("FCM_Data").Range("A1048576").End(xlUp).Row + 1
            'Confirmation et copie de la mise à jour
'          MsgBox "Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE"
            'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
            Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(ligne, 1)
            'Copier-coller la formule Date en valeur
            Sheets("FCM_Data").Select
            Range("F:F").Copy
            Range("F:F").PasteSpecial Paste:=xlPasteValues
            'Copier-coller le FCM en valeur
             Range("G:G").Copy
             Range("G:G").PasteSpecial Paste:=xlPasteValues
            'Copier-coller le Statut en valeur
             Range("H:H").Copy
             Range("H:H").PasteSpecial Paste:=xlPasteValues
            'Déselectionner la colonne F:F
            Application.DisplayAlerts = False
            'Supprimer MFC
            Columns("I:I").Select
            Cells.FormatConditions.Delete
        End If
Else
    Exit Sub
End If
    'Supprimer les lignes vides
     Call FCM_SuppressionLignes
    'Compléter la numérotation
     Call FCM_NumAuto
    'Remettre à blanc la colonne H
    Call FCM_MàJBlanc
    'Ouvrir FCM_Data
    Sheets("FCM_Data").Activate
    Range("A1").Select
    End Sub

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub FCM_NumAuto()
'Repart de la valeur la plus élevée quelque soit l'ordre de tri
  Dim i As Long, Maxi As Long
  With Sheets("FCM_Data").ListObjects("Ta_FCM_Data")
    Maxi = Application.Max(.ListColumns(1).DataBodyRange)
    For i = 1 To .ListRows.Count
      If .ListRows(i).Range(1) = "" Then
        Maxi = Maxi + 1
        .ListRows(i).Range(1) = Maxi
      End If
    Next i
  End With
End Sub

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub FCM_SuppressionLignes()
'Suppression des lignes vides
Dim i As Integer
Range("Ta_FCM_Data").Activate
For i = Selection.Cells(Selection.Cells.Count).Row _
To Selection.Cells(1).Row Step -1
   If Cells(i, "I").Value = "" Or _
      IsEmpty(Cells(i, "I").Value) Then Rows(i).Delete
Next i
End Sub

Code :

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

1
2
3
4
5
Sub FCM_MàJBlanc()
'Mise à blanc des colonnes I
Sheets("FCM_MàJ").Select
Range("I8:I43").ClearContents
End Sub

Vous trouverez attaché le classeur concerné.
Merci pour tout conseil
GDM

**21Formatic** · 30/03/2022, 17h23

Rebonjour,

Je ne me suis intéressé qu'à ta macro MàJ, pas au reste :

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
Sub FCM_MàJ()
'Ajout de la feuille FCM_Mise à Jour à FCM_data
Dim ligne As Long
Dim Rep As Integer
Rep = MsgBox("Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE")
' Réponse OK
If Rep = 1 Then
    'Tableau de destination vide
        If Range("Ta_FCM_Data").ListObject.DataBodyRange Is Nothing Then
             'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
             Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(5, 1)
             'Affecter le numéro 1 à la 1° ligne
             Sheets("FCM_Data").Range("A5").Value = 1
             'Copier-coller la formule date en valeur
 
    'Tableau de destination déjà rempli
        Else
            'Numéro de la première ligne vide de la base de données
            ligne = Sheets("FCM_Data").Range("A1048576").End(xlUp).Row + 1
            'Confirmation et copie de la mise à jour
'          MsgBox "Confirmer la mise à jour de la base des FCM", vbOKCancel, "PACTE-SSE"
            'Copy de Ta_RapMensuel_MàJ dans Ta_Rap_Mensuel
            Sheets("FCM_MàJ").ListObjects("Ta_FCM_MàJ").DataBodyRange.Copy Sheets("FCM_Data").Cells(ligne, 1)
            'Copier-coller la formule Date en valeur
        End If
Else: Exit Sub
End If
 
With Sheets("FCM_Data")
    .Range("Ta_FCM_Data[[FCM]:[STATUT]]").Value = .Range("Ta_FCM_Data[[FCM]:[STATUT]]").Value
    .Columns("H:H").Cells.FormatConditions.Delete
End With
'Compléter la numérotation
     Call FCM_NumAuto
     'Supprimer les lignes vides
     Call FCM_SuppressionLignes
    'Remettre à blanc la colonne H
    Call FCM_MàJBlanc
End Sub

Pour ce qui est de ta formule avec I4, c'est normal. Sans référence à une feuille particulière, il fera référence à la cellule I4 de la feuille sur laquelle est la formule.
Dans ton tableau feuille FCM_Màj, change la formule par :

Code :

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

=FCM_MàJ!$I$4

**Pierre Fauconnier** · 31/03/2022, 09h37

Salut.

HS: Bannir les Exit Sub svp (voir ce billet-ci et celui-là)

Pour le reste, es-tu sûr que la colonne qui pose problème existe bien dans le tableau d'arrivée (Pas d'espace en trop à droite, par ex...)?

**gdmeunier** · 31/03/2022, 22h09

Pierre Fauconnier : Merci pour les indications! N'étant qu'un utilisateur du VBA et pas un spécialiste, il me faut un peu de temps pour réfléchir. Je vais revenir. GDM

VBA Référence de cellule/Référence structurée

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager