Discussion :

[methylene67]

Bonjour,

Je souhaiterais inscrire la formule suivante dans une cellule par une action vba.

Code formule :

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

=SI(ESTNA(@INDEX(Unitemateriel;EQUIV(A20;MAT;0);0));"";INDEX(Unitemateriel;EQUIV(A20;MAT;0)))

L'enregistreur de macro m'a donné ça :

Code :

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

1
2
3
4
5
6
FormulaR1C1 = _
        "=IF(ISNA(@INDEX(Unitemateriel,MATCH(R[-3]C[-1],MAT,0),0)),"""",INDEX(Unitemateriel,MATCH(R[-3]C[-1],MAT,0)))"
 
Que je peux donc ajouter à la cellule que je souhaite remplir, en modifiant le code :
 
Cells(i,3).formulaR1C1 ="=IF(ISNA(@INDEX(Unitemateriel,MATCH(Cells(i,2).value,MAT,0),0)),"""",INDEX(Unitemateriel,MATCH(Cells(i,2).value,MAT,0)))"

unitemateriel et MAT, sont deux plages de valeurs (même nombre de lignes et 1 colonne) du même tableau défini par la fonction "Formules -> Gestionnaire de nom"

Mon soucis, c'est que dans ma formule excel initiale, la valeur de comparaison est une cellule fixe (A20), mais dans mon code, je veux que cette valeur soit la valeur contenue sur Cells(i,2).value et je n'arrive pas à remplacer le R[-3]C[-1] par cells(i,2).value.

Merci de votre retour.

[MarcelG]

Bonjour,

Personnellement, je ne suis pas favorable à l'enregistrement d'une formulation.
Et ce d'autant que la formulation R1C1 est loin d'être explicite.

Il te faut d'abord te placer dans Excel, soit la base de ton processus.

Tu peux procéder en 3 étapes
1 - Ecrire la formule au sein du tableur (exemple en "A2")
2 - Dans l'éditeur VBE (ALT + F11), activer la fenêtre Exécution (CTRL + G)
3 - Ecrire dans celle ci l'instruction (sans oublier le "?")

Code :

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

?Range("A2").Formula

Puis touche Entrée.
La formule apparaîtra

Ensuite, tu pourras écrire ta formulation en une seule instruction

Code :

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

1
2
3
4
Dim laformule As String
laformule = "la formulation obtenue ci-dessus"
 
Worksheets("lawks").range("B2:B50").Formula = laformule

Cela dit, Cela dit. Et c'est ce qu'il faut retenir en tout premier lieu

Sans VBA.
Au sein d'une table de données (tableau structuré), si la formulation s'effectue au premier enregistrement, alors elle s'incrémentera d'elle même (automatiquement) sur l'ensemble des enregistrements.
Et ce y compris sur les enregistrements à venir.
2 tutoriels à ne pas manquer
Présentation des tableaux structurés
Les tableaux structurés et VBA

[Alex020181]

Bonjour

VBA te propose 2 solution pour insérer une formule dans une cellule;
- formular1c1 qui est la méthode qu'excel utilise dans l'enregistreur de macro
- formula qui à priori correspondrait à ton besoin

Voici un exemple simplifié pour comprendre. A toi ensuite d'adapter à ton cas précis.
Mettons qu'en A5 tu veuilles faire A1+A2+A3+A4 l'enregistreur de macro te créera une formule R1C1

Code :

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

ActiveCell.FormulaR1C1 = "=R[-4]C+R[-3]C+R[-2]C+R[-1]C"

(R et C correspondent aux nombres de lignes et de colonnes à décaler par rapport à la cellule dans laquelle tu insères la formule)

Ensuite manuellement tu remplaces par
ActiveCell.Formula = "=A1+A2+A3+A4" --> la formule n'est plus en type R1C1 donc tu utilises .Formula

Et si tu veux y insérer une cellule variable comme dans ton cas (je te laisse vérifier que i est bien un nombre) il te faut l'insérer en "coupant" la formule par des "&
Exemple si A3 est variable:

Code :

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

ActiveCell.Formula = "=A1+A2+" & cells(i,2) & "+A4"

[methylene67]

Bonjour,

Merci à vous deux pour avoir pris le temps de me répondre.

@MarcelG, je suis bien d'accord, je préfère aussi éviter d'insérer des formules excel aussi complexes par le code, toutefois, j'utilisais sur l'ancien modèle de cette feuille cette formule en statique (rentrée directement sur la feuille et non par le code) et qui restait toujours dans la cellule (une ligne étant toujours soit matériel, soit fournitures, soit main-d'oeuvre et ne pouvant changer d'attribution).

Ma nouvelle feuille faisant qu'une ligne peut prendre chacune de ces attributions, l'entrée par la feuille excel n'était plus possible, et donc besoin d'insérer une formule variant selon les paramètres MO, MAT, FOUR , selon l'attribution de ma ligne (main-d'oeuvre, matériel, fournitures ...). Je pensais que cela serait plus simple et moins lourd.

J'ai donc finalement opté pour des boucles "Do Until" qui me récupèrent l'information dans le tableau de ma feuille ressources en fonction de la ligne. C'est un peu plus lourd, mais cela fonctionne très bien et ne gêne pas l'utilisation.

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
Application.EnableEvents = False
Dim col, ligne, nbrow, i, j As Integer
 
nbrow = Sheets("Ressources").Range("A" & Rows.Count).End(xlUp).Row 'Calcul la dernière ligne comportant une valeur de la colonne A
ligne = ActiveCell.Row
 
If ligne >= 11 And ligne <= 43 Then
 
    If Cells(ligne, 1).Value = "MO" Then
 
        With Cells(ligne, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=MO"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 
        With Cells(ligne, 4).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Periode"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 
         'Détermine la ligne du tableau dans l'onglet "Ressources" où l'on retrouve la valeur sélectionnée
        If Cells(ligne, 2).Value <> "" And Cells(ligne, 4).Value <> "" Then
 
            Do Until Sheets("Ressources").Cells(2, j + 1).Value = Cells(ligne, 2).Value Or j = 10
 
                j = j + 1
 
            Loop
 
            Do Until Sheets("Ressources").Cells(i + 1, 1).Value = Cells(ligne, 4).Value Or i = 10
 
                i = i + 1
 
            Loop
 
            Cells(ligne, 8).Value = Sheets("ressources").Cells(i + 1, j + 1).Value
 
        End If
 
 
    ElseIf Cells(ligne, 1).Value = "MAT" Then
 
        With Cells(ligne, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=MAT"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 
        'Détermine la ligne du tableau dans l'onglet "Ressources" où l'on retrouve la valeur sélectionnée
        If Cells(ligne, 2).Value <> "" Then
 
            For i = 1 To nbrow
 
                If Sheets("Ressources").Cells(i, 1).Value = "Matériel" Then
 
                    Do Until Sheets("Ressources").Cells(i + 1, 1).Value = Cells(ligne, 2).Value Or i = 500
 
                        i = i + 1
 
                    Loop
 
                    Cells(ligne, 4).Value = Sheets("ressources").Cells(i + 1, 3).Value
                    Cells(ligne, 6).Value = Sheets("ressources").Cells(i + 1, 2).Value
 
                    Exit For
 
                End If
 
            Next i
 
        End If
 
    ElseIf Cells(ligne, 1).Value = "FOUR" Then
 
        With Cells(ligne, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=FOUR"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 
        'Détermine la ligne du tableau dans l'onglet "Ressources" où l'on retrouve la valeur sélectionnée
        If Cells(ligne, 2).Value <> "" Then
 
            For i = 1 To nbrow
 
                If Sheets("Ressources").Cells(i, 1).Value = "Fournitures" Then
 
                    Do Until Sheets("Ressources").Cells(i + 1, 1).Value = Cells(ligne, 2).Value Or i = 500
 
                        i = i + 1
 
                    Loop
 
                    Cells(ligne, 4).Value = Sheets("ressources").Cells(i + 1, 3).Value 'Indique l'unité de la ressource choisie
                    Cells(ligne, 10).Value = Sheets("ressources").Cells(i + 1, 2).Value 'Indique le prix unitaire de la ressource choisie
 
                    Exit For
 
                End If
 
            Next i
 
        End If
 
    ElseIf Cells(ligne, 1).Value = "SST" Then
 
        With Cells(ligne, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=SST"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 
        'Détermine la ligne du tableau dans l'onglet "Ressources" où l'on retrouve la valeur sélectionnée
        If Cells(ligne, 2).Value <> "" Then
 
            For i = 1 To nbrow
 
                If Sheets("Ressources").Cells(i, 1).Value = "Fournitures" Then
 
                    Do Until Sheets("Ressources").Cells(i + 1, 1).Value = Cells(ligne, 2).Value Or i = 500
 
                        i = i + 1
 
                    Loop
 
                   Cells(ligne, 4).Value = Sheets("ressources").Cells(i + 1, 3).Value 'Indique l'unité de la ressource choisie
                    Cells(ligne, 14).Value = Sheets("ressources").Cells(i + 1, 2).Value 'Indique le prix unitaire de la ressource choisie
 
                    Exit For
 
                End If
 
            Next i
 
        End If
 
 
    Else
 
        'Si on supprime le type, remet à 0 la ligne
         Cells(ligne, 2).Validation.Delete
         Cells(ligne, 4).Validation.Delete
         Cells(ligne, 2).Value = ""
         Cells(ligne, 4).Value = ""
         Cells(ligne, 5).Value = ""
         Cells(ligne, 6).Value = ""
         Cells(ligne, 8).Value = ""
         Cells(ligne, 10).Value = ""
         Cells(ligne, 14).Value = ""
 
    End If

@Alex020181, je te remercie, d'avoir éclairci la définition de R[-1]C que je ne comprenais pas.

J'ai essayé par curiosité en adaptant à mon besoin, mais la formule n'affiche pas de valeur, il me renvoie un #CHAMP!

Code :

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

Cells(ligne, 4).Formula = "=IF(ISNA(INDEX(Unitemateriel,MATCH(" & Cells(ligne, 3) & ",MAT,0),0)),"""",INDEX(Unitemateriel,MATCH(" & Cells(ligne, 3) & ",MAT,0)))"

Voilà ce que m'affiche la formule dans cellule après entrée du code : =SI(ESTNA(@INDEX(Unitemateriel;EQUIV(;MAT;0);0));"";INDEX(Unitemateriel;EQUIV(;MAT;0)))

Il ne prend pas en compte les cellules. Par ailleurs pourquoi le premier index à un @ ?

[methylene67]

J'ai finalement trouvé :

Code :

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

Cells(ligne, 4).Formula = "=IF(ISNA(INDEX(Unitemateriel,MATCH(B" & ligne & ",MAT,0),0)),"""",INDEX(Unitemateriel,MATCH(B" & ligne & ",MAT,0)))"

Qui m'affiche sous excel.

=SI(ESTNA(@INDEX(Unitemateriel;EQUIV(B12;MAT;0);0));"";INDEX(Unitemateriel;EQUIV(B12;MAT;0)))

Toutefois, si la colonne devait varier, je ne sais pas comment faire (puisque excel reconnait un "A", "B", "C", ...) alors que le vba donne la colonne avec des chiffres ("1", "2", "3", ...).

[MarcelG]

Bonjour Metylene,

2 remarques au préalable.
- Ne fais pas comme moi, adopte immédiatement les tableaux structurés pour une meilleure efficacité dans la gestion des données (End(xlUp) = Finished)
- Prends cette bonne pratique de toujours rattacher les objets à leur parent. A minima, les objets Range à leur feuille Worksheet.

Je trouve ton code beaucoup trop complexe.

Si tu peux retourner un fichier simplifié, ne comportant pas de données confidentielles, alors je pourrais - peut-être - te proposer un code plus simple
(Do Until .. j = j+1, pas aimer )

A plus tard

[methylene67]

Oui, en effet le stableaux structurés sont bien plus simples une fois maîtrisés, mais dans mon cas ils m'auraient compliqués la vie :

• J'aurais pu créer une colonne de "type d'articles" : Fournitures, main d'oeuvre, matériel, sous-traitance, et donc mettre tout dans un seul tableau structuré
• Mais j'aurais eu le problème des listes de validation variable que je n'aurais su gérer (sans chercher)
• J'aurais également du réaliser un double tri (d'abord sur les type d'article, puis ensuite sur les articles par type d'articles en alphabétique), que là aussi en premier lieu je ne sais pas faire sans recherches

Pour la boucle do while, ça fait un bail que je n'ai pas fait de VBA, et j'ai pris ce dont je me souvenais, après si le j + j + 1, autant le remplacé par un for + if, ça fait le même boulot avec plus de ligne de code.

Enfin, pour les Range, je les utilise le moins possible, je préfère travailler sur les cellules directement, mais certaines fonctions/arguments obligent de déclarer d'utiliser des Range (comme le tri par exemple). D'ailleurs, je ne vois pas où tu as vu des Range non rattaché à leur feuille, par contre mes cells ne sont effectivement pas rattaché à ActiveSheet. ou Me., je trouve pour le cas cela plus lourd et non nécessaire, mais le fait de les mettre est sans doute une bonne habitude en programmation.

Je sais aussi que tu vas me dire que les cellules fusionnées c'est à éviter, mais les mauvaises habitudes demeurent ...

Je t'ai mis les deux fichiers avec l'utilisation des formules excel injectées via le code(donc sans boucle do while), et celui avec recherche par le code. La recherche dans un tableau structuré serait indéniablement plus simple d'un point de vue code.

L'onglet "modèle" est mon ancien modèle, le modèle 2 est celui sur lequel je travaille. Ne pas prendre en compte l'onglet BPU, je vais tout remodifié, c'était un truc fait à la va vite il y a 1 an et demi.Fichier.xlsmFichier avec formule.xlsm

[Pierre Fauconnier]

Salut.

Perso, je n'ai même pas compris ce que tu souhaitais réaliser, mais je suis assez bien convaincu qu'il y a plus simple, surtout si on réfléchit d'abord à la finalité, à l'objectif à atteindre.

Passer par une constante qui doit être retraitée en variable me semble pour le moins rocambolesque.

[MarcelG]

Bonsoir,

je présume que le numéro de ligne de la formule doit être le même que le numéro de ligne du début du range pour que cela s'incrémente

Ben oui!

Cela revient à écrire la formule dans la 1ère cellule de destination, et recopier celle-ci (croix à l'angle inférieur droit) sur toute la plage.

Mais ce processus s'exécute en une fois, d'où son intérêt.

Comme le suggèrerait Pierre, (salut!), il faudrait mieux reporter une vue d'ensemble de
- ce dont tu disposes (les données de départ)
- le résultat attendu
Ce qui revient à reporter ton développement dans son ensemble.

Bonne soirée.