Erreur insérer une formule dans une cellule avec VBA

Bonjour,

Je veux insérer une formule dans une cellule avec un code VBA.
J'ai une erreur au niveau de la ligne avec ma formule, je ne comprends pas.

Code erreur :

Run-time error '1004':
Application-defined or object error

Lorsque je mets la formule directement dans la cellule ça fonctionne très bien. Lorsque je change ma formule pour une plus basique (="=2+2") cela marche très bien.

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Sub addformula()     lr = Worksheets("data label").Range("T3").Value   Sheets("print").Select   Range("I2").Formula = "=IFERROR(INDEX('data label'!$H$2:$H$700,MATCH(1,SIGN(COUNTIF($I$1:I1,'data label'!$H$2:$H$700)<SUMIF('data label'!$H$2:$H$700,'data label'!$H$2:$H$700,'data label'!$J$2:$J$700)),0)),"")"   Range("I2").AutoFill Range("i2:i" & lr) End Sub

---

Cette formule me permet d'avoir une une valeur qui se copie X fois en fonction d'une valeur "repeat".
Je veux ensuite que cette formule se mette dans X cellules an fonction de la valeur "lr". Mais cette partie du code fonctionne tres bien, c'est vraiment la formule que Excel me laisse pas entrer.

Merci beaucoup

J'ai résolu une partie du problème en doublant les ":

Code:

---

Range("H2").Formula = "=IFERROR(INDEX('data label'!$H$2:$H$700,MATCH(1,SIGN(COUNTIF($H$1:H1,'data label'!$H$2:$H$700)<SUMIF('data label'!$H$2:$H$700,'data label'!$H$2:$H$700,'data label'!$J$2:$J$700)),0)),"""")"

---

La formule est bien copiée dans la cellule, mais Excel m'insère des @ :

Code:

---

=IFERROR(INDEX('data label'!$H$2:$H$700,MATCH(1,SIGN(COUNTIF($H$1:H1,@'data label'!$H$2:$H$700)<SUMIF('data label'!$H$2:$H$700,@'data label'!$H$2:$H$700,'data label'!$J$2:$J$700)),0)),"")

---

CE qui fait en sorte que ma formule ne fonctionne plus

Bonjour labaguette

Pour ce faire, personnellement, je procède en 3 étapes

1 - J'écris la formule en 1 cellule qui me retourne le résultat escompté
Exemple, en cellule B4, le nombre de caractères de l'exporession "La petite marchande"

Citation:

---

=NBCAR("la petite marchande")

---

2 - Pour le langage VBA, je reporte cette formule dans la fenêtre Exécution de l'espace VBE, activé par la composition de touches CTRL + G

Code:

---

?Sht_calcul.range("B4").Formula

---

(ne ps oublier le point d'interrogation)

3 - Je reporte dans une procédure l'évaluation de cette formule

Code:

---

1 2 3 4 5

Option Explicit   Sub test_formula() Sht_calcul.Range("B4").Value = Evaluate("LEN(""La petite marchande"")") End Sub

---

Ne pas oublier
1 - de retirer le signe "="
3 - de double quotter les expressions des libellés comprises dans le paramètre de la méthode Evaluate

Ce processus permet de s'affranchir de formules dans le tableau

Je te laisse adapter ce processus à ton cas.

Pour ton information, Excel, dans ses versions récentes, force la formulation en formulation de tableau structuré.
Cette modification automatique, qu'il faut conserver, est une invitation à utiliser ce mode de présentation bien utile et à considérer automatiquement pour toutes données issues de base avec champs et enregistrements

Tu remarqueras au passage que je n'utilise pas le nom de la feuille de classeur tel qu'il figure dans le classeur mais son Code Name tel qu'il figure fans l'explorateur de projets
A ce sujet, tu peux consulter le billet de l'ami Philippe Thulliez, que je salue au passage. :ccool:

A bientôt et bonne intégration au Forum

Bonjour,
Bonjour,

Autre solution ( Celle que j'utilise toujours quand je dois ensuite l'utiliser dans VBA :
Mettre au point ta formule dans une cellule.
Quand elle est au point, copier le contenu de la formule dans la barre de formule.
Puis aller dans le Gestionnaire de Nom et créer un nouveau nom sur cette cellule. (Galopin, Libellule, Papillon peu importe...) et dans la zone Fait référence à : Coller ta formule puis valider.
Ensuite TAPUKA remplacer ta formule dans la cellule par
= Galopin
ou
= Papillon...
et dans VBA idem :
Range("blabla").Formula = "=Galopin"
Plus simple YAPA : Surtout si c'est une formule qui doit s'incrémenter sur plusieurs lignes ou plusieurs colonnes.

A+

Salut.

Citation:

---

Envoyé par galopin01

...

---

Sauf à n'avoir rien compris à la méthode proposée, j'ai de très doutes sur cette méthode, dès lors qu'elle utilise des références autres que des références structurées. De plus, elle crée inutilement un Name dans le classeur... Je doute fort que cette technique soit applicable avec des références relatives ou lorsqu'il faut remplacer des valeurs "en dur dans la cellule" par des variables en concaténant la formule en anglais. Peut-être serait-il judicieux de donner quelques exemples de formules "qui fonctionnent"?

Perso, j'utilise la démarche de Marcel. J'utilise juste une fonction générique pour remplacer les textes en dur par des variables.

Bonjour Pierre,
Je n'ai pas aujourd'hui d'exemple pertinent à te soumettre parce que je suis à la retraite depuis belle lurette donc j'ai des besoins personnels assez modeste en matière d'Excel et VBA.
Cependant j'ai utilisé Excel et VBA (mais pas que...) depuis les toutes premières versions des années 90. Et depuis une bonne vingtaine d'années j'ai participé sur de nombreux forums essentiellement comme helper.
A ce titre j'ai du soumettre cette méthode plusieurs dizaines/centaines? de fois à de nombreux demandeurs qui se les ont appropriées.
Évidemment cela crée quelques formules nommées... Mébon c'est le plus souvent bien accepté par les gens qui les utilisent.
Surtout si la formule est complexe, c'est le cas de notre ami, il est parfois difficile de la traduire en VBA. Quand on n'a pas 20 ans de pratique, on privilégie souvent les méthodes les plus simples...
Egalement si on doit les utiliser avec VBA dans des grands tableaux comptables et pas forcément des tableaux structurés (qui n'en ont pas besoin puisque les formules s'étendent d'elles-même)
Ainsi j'utilise beaucoup les tableaux de formules de base de données (BDSOMME...) Comme les zones de critères comportent à minima 2 lignes et que les lignes comptables en comptabilité peuvent compter plusieurs centaines de comptes, on peut arriver facilement à plusieurs milliers de lignes de formules, le plus souvent cachées dans une feuille de paramètres, mais qui doivent être rafraichies au gré des comptes qui peuvent se créer...
On peut objecter que les TCD et autres joyeusetés (SOMMEPROD, formules matricielles...) mébon : Moi je trouves ces formules extrêmement rapides et compétitives et quelle souplesse ! Tu peux gérer une vingtaine de comptes de tutelles multibancarisés avec une petite dizaine de formules nommées... Et tu fais ainsi passer un classeur ventripotent de quelques dizaines de Mo à 1ou 2.
Après je n'ai pas la prétention d'être un pro ni de maitriser tous les gadgets 2.0 ou 5D... Je suis juste pragmatique !
Je propose après chacun voit midi à sa porte...
Cordialement.

Citation:

---

Envoyé par galopin01

...

---

C'est pourquoi je demandais l'un ou l'autre exemple, car il semble que je n'ai manifestement pas compris la manoeuvre que tu préconises.

@ Galopin,


J'ai relu la question initiale et j'avais (mal) compris que la formule devait être adaptée côté VBA. A partir du moment où elle ne doit pas l'être, je suis d'accord avec toi sur la méthode.


@labaguette,

Tu gagnerais à utiliser les tableaux structurés. Une formule en VBA qui fige les données aux lignes 2:700 et qui ailleurs utilise toute la colonne I:I ne me semble pas idéale.