Copie de formule du code VBA vers une cellule avec Cells(,)

**Omnbre** · 05/02/2016, 14h03

Bonjour à tous,

Tout d'abord petite explication générale de l'utilisation de VBA dans mon cas :

J'ai plusieurs feuilles avec des formats de tableaux différent qui sont lié par des calculs très simples. La macro va me permettre malgré l'éventuel décalage entre colonnes de trouver les valeurs de la bonne cellule. Par exemple j'ai deux tableaux 3(col.)*3(lin.) où ma formule est en deuxième colonne. La formule pointe vers une autre feuille sur deux tableau 1*1. si l'on effectue bêtement la copie de la formule entre les deux premiers tableaux, on trouvera un mauvais résultats dans le second vu qu'il pointera dans le vide.

Pour cela j'ai monté cette macro mais j'ai un petit souci lié je pense à la fonction Cells. Tout d'abord voici mon code :

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
For i = 0 To n - 1
 
frett_def = "Empilage!" & Cells(29, 67 + i * 3 + i)  'Localisation du frettage en definition
        Dext = "Empilage!" & Cells(15, 67 + i * 3 + i)  'Localisation du diametre exterieur de la piece male
        frett_serv = "Empilage!" & Cells(199, 67 + i * 3 + i)  'Localisation du frettage en service
 
        Cst_fmont = "Constantes_de_frettage!" & Cells(18, 69 + i)
        Cst_fmef = "Constantes_de_frettage!" & Cells(38, 69 + i)
        Cst_ffonc = "Constantes_de_frettage!" & Cells(58, 69 + i)  'Localisation des constantes de frettages des pieces femelles au montage en mef et en service
 
        Cst_Mmont = "Constantes_de_frettage!" & Cells(10, 69 + i)
        Cst_Mmef = "Constantes_de_frettage!" & Cells(30, 69 + i)
        Cst_Mfonc = "Constantes_de_frettage!" & Cells(50, 69 + i)  'Localisation des constantes de frettages des pieces males au montage en mef et en service
 
        P_serv = frett_serv & "/" & Dext & "/" & "(" & Cst_ffonc & "-" & Cst_Mfonc & ") * 0.001" 'Explicitation de la formule de la pression de service
        xlBook.Sheets.Item("Empilage").Cells(198, 3 + 3 * i + i) = P_serv  'Insertion dans la cellule de destination
 
        y = frett_def & "/" & Dext & "/" & "(" & Cst_fmont & "-" & Cst_Mmont & ") * 0.001" 'Explicitation de la formule de la pression de contact au montage
        xlBook.Sheets.Item("Detail_des_effets").Cells(57, 3 + 3 * i + i) = y  'Insertion dans la cellule de destination
 
        y = frett_def & "/" & Dext & "/" & "(" & Cst_fmef & "-" & Cst_Mmef & ") * 0.001" 'Explicitation de la formule de la pression de contact en mef
        xlBook.Sheets.Item("Detail_des_effets").Cells(68, 3 + 3 * i + i) = y  'Insertion dans la cellule de destination
 
        y = frett_serv & "/" & Dext & "/" & "(" & Cst_ffonc & "-" & Cst_Mfonc & ") * 0.001" 'Explicitation de la formule de la pression de contact en service
        xlBook.Sheets.Item("Detail_des_effets").Cells(79, 3 + 3 * i + i) = y  'Insertion dans la cellule de destination

Le but de ce code est de localiser tout d'abord des valeurs présente dans les cellules de mon fichier puis d'effectuer des calcules relativement simples avec une incrémentation.
Seul petit souci lors de l’exécution de cette macro, dans les de destination, le calcul n'est pas fait. En effet lorsque je veux envoyer mes résultats de "P_serv" ou "y" dans leur cellules respectives on trouve écrit "Empilage!/Empilage!/(Constantes_de_frettage!-Constantes_de_frettage!) * 0.001".

Le signe "=" a disparu. Seulement, lorsque je le rajoute, une erreur se produit dans l'exécution de la macro (Erreur de syntaxe).

Cela est il lié à la fonction Cells? y'a t il un moyen de le contourner?

Merci d'avance pour vos réponses! (Au besoin le fichier complet est joint).

Omnbre

**Daniel.C** · 05/02/2016, 14h16

Bonjour,

Avant que je lise tout, peut-être que l'utilisation de INDIRECT te simplifiera la vie :

Code :

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

=INDIRECT("Feuil1!A1")

pointe sur la cellule A1 de Feuil1 où que tu recopies la formule.

**Omnbre** · 05/02/2016, 14h22

Bonjour, et merci de ta réponse rapide

Malheureusement j'avais déjà entrepris d'utiliser cette fonction mais elle ne se prête pas à mon cas

L'explication que j'ai fourni n'est pas très claire et je pense que regarder le fichier excel t'éclaireras plus!

Merci!

Omnbre

**Daniel.C** · 05/02/2016, 16h37

OK

Quand je descends ton code, la première cellule (C198) contient :

Code :

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

Empilage!/Empilage!/(Constantes_de_frettage!-Constantes_de_frettage!) * 0.001

qui correspond à la variable "P_serv". Or quand tu écris :

Code :

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

xlBook.Sheets.Item("Empilage").Cells(198, 3 + 3 * i + i)

C'est comme si tu écrivais (c'est sous-entendu) :

Code :

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

xlBook.Sheets.Item("Empilage").Cells(198, 3 + 3 * i + i).Value

alors qu'il faut mettre :

Code :

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

xlBook.Sheets.Item("Empilage").Cells(198, 3 + 3 * i + i).Formula

Ensuite il faut faire précéder "P_serv" d'un "=", comme toutes les formules. Cependant, ça ne fonctionne pas encore. Dis-moi la formule que tu souhaites savoir en C198.

**Omnbre** · 05/02/2016, 16h48

Ah oui entendu pour le .formula, cela permet donc d'expliciter la formule?
Pour le résultat que je souhaite :

Code :

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

Empilage!C198 = Empilage!C199/Empilage!C15/(Constantes_de_frettage!E58-Constantes_de_frettage!E50)*0.001

Effectivement je viens de me rendre compte d'une autre erreur dans mon code. J'avais à la base utiliser des chr et donc référencé les numéro de colonne en ascii Pour les 9 premiers calculs il faut donc remplacer la deuxième référence de cellule par la réf ascii.
On trouve donc pour les trois premiers calculs en second termes : 3+3*i+i et pour les 6 autres calculs 5+i.

**Daniel.C** · 05/02/2016, 17h19

Est-ce que ça résout complètement ton problème ?

Tu écris :

Code :

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

Empilage!C198 = Empilage!C199/Empilage!C15/(Constantes_de_frettage!E58-Constantes_de_frettage!E50)*0.001

Ce n'est pas une formule. Est-ce que, en C198, la formule doit être :

Code :

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

=Empilage!C199/Empilage!C15/(Constantes_de_frettage!E58-Constantes_de_frettage!E50)*0.001

Actuellement, "P_serv" contient :

"Empilage!/Empilage!/(Constantes_de_frettage!-Constantes_de_frettage!) * 0.001"

Où est-ce que je prends E50 et E58 ? ce sont des constantes ?

**Omnbre** · 05/02/2016, 18h10

Pour ce qui est de la correction des numéro de colonne le problème est résolu en partie.

Pour ce qu'il y a dans la cellule correspodant à P_serv c'est bien cela que je veux. Je t'avais noté la formule de cette façon pour bien explicité ce que je voulais dans la cellule.

E50 et E58 sont deux valeurs que tu trouve dans l'onglet Constantes_de_frettage.
Elles sont déclarées dans le code VB. (Du moins le contenu de leurs cellules sont récupérées pour calculer P_serv.).

**Daniel.C** · 05/02/2016, 20h52

Je ne comprends pas. Tu vois bien que l'adresse des cellules manque dans P_serv.

Si je modifie frett_serv :

Code :

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

frett_serv = "Empilage!" & Cells(199, 67 + i * 3 + i).Address(0, 0)

j'obtiens (par exemple) :

"Empilage!BO199"

et pour P_serv :

"Empilage!BO199/Empilage!/(Constantes_de_frettage!-Constantes_de_frettage!) * 0.001"

E50 et E58 sont deux valeurs que tu trouve dans l'onglet Constantes_de_frettage.
Elles sont déclarées dans le code VB. (Du moins le contenu de leurs cellules sont récupérées pour calculer P_serv.)

D'après ce que tu as écrit plus haut, ce n'est pas leur contenu, mais leur adresse qu'il faut.

**Omnbre** · 06/02/2016, 01h17

Ah oui! je comprends!

Comme dit dans un de mes précédent post j'avais fait une erreur.
A la base j'avais programmé mon code avec la fonction chr qui reprends non pas le numéro de colonne mais la lettre grâce au code ascii.
Etant changé avec la fonction cells j'ai malencontreusement oublié de changer la numérotation.

Donc pour le code :

Code :

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

frett_serv = "Empilage!" & Cells(199, 67 + i * 3 + i).Address(0, 0)

il faut le remplacer par :

Code :

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

frett_serv = "Empilage!" & Cells(199, 3 + i * 3 + i).Address(0, 0)

67 en code ascii équivalent à la colonne C. Le problème est alors que si je dépasse la numérotation ascii au delà du Z, mon code présente un gros dysfonctionnement (normal vu que le AA n'est pas vraiment référencé en ascii...).

donc tu devrais obtenir :

"Empilage!C199"

Pour l'autre parti, regarde en faisant varier la cellule du nombre de pièces puis en faisant fonctionner la macro. Tu va pouvoir observer qu'il y a une création de tableaux supplémentaires. Le nombre de tableau sera alors égal à cette valeur. Il en est de même avec les colonnes présentes dans l'onglet Constantes_de_frettage (C'est d'ailleurs là qu'apparait le décalage entre les feuilles).

Effectivement ce sera alors leur adresse que je veux récupérer dans le code VBA et indirectement leur valeur puisqu'elle est nécessaire pour la suite du calcul.

Merci en tout cas de te pencher sur mon sujet et j'espère que celui est plus clair après cette explication.

Cordialement et amicalement

Omnbre

**Daniel.C** · 06/02/2016, 11h00

Bonjour,

Voilà. Je n'ai modifié que ce qui concernait C198 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
        frett_def = "Empilage!" & Cells(29, 67 + i * 3 + i).Address(0, 0)  'Localisation du frettage en definition
        Dext = "Empilage!" & Cells(15, 3 + i * 3 + i).Address(0, 0)  'Localisation du diametre exterieur de la piece male
        frett_serv = "Empilage!" & Cells(199, 3 + i * 3 + i).Address(0, 0) 'Localisation du frettage en service
 
        Cst_fmont = "Constantes_de_frettage!" & Cells(18, 5 + i)
        Cst_fmef = "Constantes_de_frettage!" & Cells(38, 5 + i)
        Cst_ffonc = "Constantes_de_frettage!" & Cells(58, 5 + i).Address(0, 0) 'Localisation des constantes de frettages des pieces femelles au montage en mef et en service
 
        Cst_Mmont = "Constantes_de_frettage!" & Cells(10, 5 + i)
        Cst_Mmef = "Constantes_de_frettage!" & Cells(30, 5 + i)
        Cst_Mfonc = "Constantes_de_frettage!" & Cells(50, 5 + i).Address(0, 0)  'Localisation des constantes de frettages des pieces males au montage en mef et en service
 
        P_serv = frett_serv & "/" & Dext & "/" & "(" & Cst_ffonc & "-" & Cst_Mfonc & ") * 0.001" 'Explicitation de la formule de la pression de service
        xlBook.Sheets.Item("Empilage").Cells(198, 3 + 3 * i + i).Formula = "=" & P_serv   'Insertion dans la cellule de destination

**Omnbre** · 08/02/2016, 09h23

Bonjour!

J'ai tester ce que tu me propose et c'est parfait concernant l'incrémentation. Je n'ai plus de problème de décalage.

Il me reste juste une petite erreur. Quand je vais vérifier la formule dans C57 je trouve :

=C29/C15/(E18-E10) * 0.001

Le problème est, et c'est valable pour P_serv ainsi que les trois y, que la formule va chercher les valeurs dans la feuille "Detail_des_effets" alors qu'il est censer récupérer les données dans "Empilage" et "Constantes_de_frettage". Plus clairement pour cet exemple on devrait avoir :

=Empilage!C29/Empilage!C15/(Constantes_de_frettage!E18-Constantes_de_frettage!E10) * 0.001

Est-ce la fonction "Adress" qui fausse la référence alors que normalement ces valeurs sont bien récupérées au bon endroit?

Cordialement

Omnbre

**Omnbre** · 08/02/2016, 11h19

Re-bonjour et rectification!

J'ai fait moi même une petite modif (car effectivement toutes les référence n'y étais pas si je voulais l'appliquer pour d'autre cas que P_serv)

Donc ce coup ça marche impeccable!!

Encore merci et bonne jounrée!

Cordialement

Omnbre

Copie de formule du code VBA vers une cellule avec Cells(,) [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager