VBA Insérer une formule "dynamique" dans une cellule Excel

  • Auteur de la discussion Philou250
  • Date de début

Philou250

Habitué
Bonjour,

Je souhaiterai insérer dans une cellule une formule par le biais d'une macro VBA.
J'ai un problème car cette formule varie en fonction du nombre de données, pour essayer d'être plus clair, voici un exemple :
Sur une feuille Excel nous avons (il faut imaginer un tableau Excel^^) :
==A====B=====C====D=====E
1=====25.4===30===30.48==914.4
2======1===1.18===12=====36
3

Et grâce à une macro, on souhaiterait avoir la formule de la cellule A3 qui soit égale à B1*B2+C1*C2+D1*D2+... (tant qu'il y des valeurs).

Est-ce possible ?

Merci d'avance.

Philou250


 

drul

Obscur pro du hardware
Staff
Salut,
Voici un début de réponse:
Code:
Range("A3").Formula = "=A1*A2"

Ensuite à toi de créer la formule désirée à l'aide d'une boucle.
Si tu sais pas comment faire, dis moi et je t'aide. MAis commence par me présenter une ébauche de programme.
 

Philou250

Habitué
J'ai écris ce code :

Code:
Private Sub CommandButton1_Click()
Dim Cell1(20) As Range
Dim a As Single
Dim n As Single

Set Cell1(1) = Worksheets("Sheet1").Range("B1")
n = 1
While Cell1(n).Value <> ""                                'Boucle qui permet de connaître le nombre 
Set Cell1(n + 1) = Cell1(n).Offset(1, 0)              'de cellules qui vont être dans la formule de A1
n = n + 1
Wend
n = n - 1
For a = 1 To n
Range("A1").Formula = "=" & Cell1(a).Name & "+" & Cell1(a+1).Name ...
Next
End Sub

Je n'arrive pas à trouver le raisonnement pour incrémenter toutes les cellules automatiquement une formule.

Merci pour ton aide.
 

drul

Obscur pro du hardware
Staff
Ok, c'est un bon début ...

Bon les deux boucles sont inutiles, une seule suffirait, mais on y viendra plus tard.

Ton probleme est bien entendu dans la boucle for.

le but de cette boucle doit être de creer ta formule, en ne lui rajoutant qu'une seule cellule à chaque itération.

Tu dois t'inspirer de la formule suivante:

A=A+1

Essaie encore un peu. Le mieux c'est quand même d'apprendre par soi-même. (si tu y arrives tjrs pas, alors je te donnerai une solution).
 

Philou250

Habitué


Du coup j'ai écris ça :
Code:
For a = 1 To n
Range("A1").Formula = "=" & Range("A1").Formula & "+" & Cell1(a).Name
Next

Il semblerait qu'Excel n'aime pas trop le .Name derrière le Cell1(a)
 

drul

Obscur pro du hardware
Staff
Re-Salut,

On avance, très bien.

Premièrement tu vas avoir beaucoup de "=" dans ta formule ... Initialise la formule en dehours de la boucle

Ensuite pour une question de vitesse d'execution, cree ta formule dans une variable de type string, puis finalement tu copie ta string dans la formul de ta cellule.

Finalement, utilise "address" au lieu de "name" ...
 

Philou250

Habitué
Code:
Range("A1").Formula = "="               'Initialisation de la formule de A1
For a = 1 To n
    CellFormule = Range("A1").Formula   'Récupération de "l'ancienne" formule
    CellFormule = CellFormule & "+" & Cell1(a).Address      'Ajout d'une cellule dans la formule
    Range("A1").Formula = CellFormule       'Ajout de la nouvelle formule dans la cellule A1
Next

Ca marche!!!
J'obtiens ceci, exemple : =+$B$1+$B$2+$B$3+$B$4
Y-a-t'il certaines choses à perfectionner ?

Merci beaucoup
 

drul

Obscur pro du hardware
Staff


Euh, tu as pas bien compris l'utilité de la variable temporaire ... le but est d'éviter d'écrire un nombre important de fois dans la range, ce qui est très lent ...

Essaie plutot le code ci-dessous ...

Code:
CellFormule  = "="               'Initialisation de la formule de A1
For a = 1 To n
    CellFormule = CellFormule & "+" & Cell1(a).Address      'Ajout d'une cellule dans la formule
Next
    Range("A1").Formula = CellFormule       'Ajout de la nouvelle formule dans la cellule A1
 

drul

Obscur pro du hardware
Staff
REste plus qu'a éliminé la première boucle:

Etudie l'expression suivante:

Code:
n = Cells(2, 255).End(xlToLeft).Column

Pis en regardant ton, c'est quoi cette histoire de déclarer un tableau de 20 cellule ? il se passera quoi si tu as plus de 20 cellule utilisé ?
 

Philou250

Habitué
Connais-tu une variante à cette formule qui permettrerait de connaitre le nombre de données consécutives, et non le nombre de données qu'il y a sur une ligne entière.

Je m'explique : dans le fichier Excel, il y a d'autres données sur la ligne 1 qui ne doivent pas rentrer dans la formule de la cellule. Entre ces deux types de données, il y a une colonne qui n'est pas utilisée (="").

C'est pour ça que j'utilisais le while Cell1... <> ""
 

drul

Obscur pro du hardware
Staff
La même, mais en partant depuis la gauche (cells(2,1), ou plutôt cells(2,x) ou x est la première colonne qui t'intéresse) et en utilisant xlToRight.

 
Vous devez vous inscrire ou vous connecter pour répondre ici.
Derniers messages publiés
Statistiques globales
Discussions
730 098
Messages
6 717 055
Membres
1 586 282
Dernier membre
Yannick3553
Partager cette page
Haut