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

Version imprimable

1 pièce(s) jointe(s)

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:

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

05/02/2016, 14h16
Daniel.C

Bonjour,

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

Code:

=INDIRECT("Feuil1!A1")

pointe sur la cellule A1 de Feuil1 où que tu recopies la formule.
05/02/2016, 14h22
Omnbre

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
05/02/2016, 16h37
Daniel.C

OK 8O

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

Code:

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

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

Code:

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

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

Code:

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

alors qu'il faut mettre :

Code:

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.
05/02/2016, 16h48
Omnbre

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

Code:

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.
05/02/2016, 17h19
Daniel.C

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

Tu écris :

Code:

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:

=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 ?
05/02/2016, 18h10
Omnbre

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.).
05/02/2016, 20h52
Daniel.C

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

Si je modifie frett_serv :

Code:

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"

Citation:

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.
06/02/2016, 01h17
Omnbre

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:

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

il faut le remplacer par :

Code:

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

Bonjour,

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

Code:

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

08/02/2016, 09h23
Omnbre

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
08/02/2016, 11h19
Omnbre

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