Insertion de formule avec .formula avec variable

Version imprimable

26/02/2019, 16h21
JohannCOU

1 pièce(s) jointe(s)

Insertion de formule avec .formula avec variable

Bonjour à tous,

Je me permet de faire appel à vous car je galère sur un problème (bénin pour beaucoup d'entre vous, je pense).

Le contexte: Je récupère des nomenclatures au format CSV, extraite d'un logiciel de conception, que je doit travailler pour pouvoir les insérer en automatique dans notre ERP. Pour cela, j'ouvre par macro le CSV, j'ouvre un modèle où il y a des tableaux permettant de faire des conversions par (=RECHERCHEV). Je copie la feuille du fichier CSV que je ferme ensuite puis je cherche à transformer les données de la feuille "Nomenclature brute" à la feuille "Nomenclature à insérer", pour ensuite enregistrer cette dernière dans un classeur indépendant du modèle.

Le problème: Pour transformer les données, je voudrais insérer des formules dans les cellules mais cela ne fonctionne pas comme je veux. J'ai enregistrer une macro et cela me sort du formulaR1C1 que je ne trouve pas lisible et je n'arrive pas à insérer ma variable "li" pour le choix de la ligne (Inutile d'ailleurs en R1C1, il me semble). Donc je souhaite passer par .formula pour écrire les expressions comme dans les cellules. J'ai privilégié .formula à .formulalocal pour ne pas avoir de problème avec des personnes qui auraient des versions anglaises.

Si quelqu'un peut m'aider à résoudre mon problème. ça serait cool.

Je vous joint mon fichier avec une feuille supplémentaire "Nomenclature MAIN" pour vous montrer le résultat que j'attends.

Pièce jointe 453338

PS: Le code est en court. je n'ai pas été au bout. j'attends de voir si je m'engage dans la bonne direction.

Je vous remercie par avance pour votre aide.

Johann

Bonjour

exemple simpliste

Code:

1
2
3
4
Dim i as long
 
i=2
Worksheets("NomFeuille").range("A3").Formula="=SUM(A1:A" &i &")"

Bonjour,

Lorsque l'on débute et que l'on veux insérer une formule avec FormulaR1C1 ou Formula c'est prenant
car il faut mettre en anglais et calculer lignes/ colonnes.

Le plus simple, c'est d'utiliser FormulaLocal qui fonctionne avec le français par exemple
Code:

1 2 3 4 5 6 Dim der As Long With Sheets("Feuil1") der = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("A1").FormulaLocal = "=SOMME(A2:A" & der & ")" .Range("D2:D" & der).FormulaLocal = "=A2*B2" End With
Il n'y a ainsi qu'à copier la formule créée sur la feuille en incluant les variables.
J'ai rajouté la possibilité de mettre la formule sur toute la plage si la formule peut s'étendre sans avoir à boucler sur les lignes.

27/02/2019, 08h04
JohannCOU

RE

Bonjour,

Merci mais il me semble que j'avais compris tout ça. Le problème est que je n'arrive pas à l'appliquer à mon exemple.

Est-ce que quelqu'un peut me dire pourquoi?

Merci d'avance.
27/02/2019, 08h22
mercatog

Parceque tu ne l'as pas appliqué correctement.
Ecrit ta formule et remplaces les constantes par tes variables en les entourant de "& variable &"

Ne pas mélanger les , et les ; dans les formules

Doubler les " quand elles sont dans la formule initiale

Fais d'abord des debug.print pour voir le résultat obtenu pour la chaine de caractère composant la formule

Voila des conseils à appliquer et pas besoin de ton fichier

Merci mercatog pour tes conseils,

A force de bidouiller je me suis rendu compte que mon problème venait de certaines lignes qui fonctionnent en formulalocal mais pas en formula malgré la traduction. Cela me semble bizarre mais bon je vais tout passer en formulalocal pour ne plus avoir de problème.

Voici mon code avec les lignes qui ne fonctionnent pas en commentaire et celle qui fonctionne juste au-dessus.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub TRANSFORMATION() 'Travail de la nomenclature Macro
 
nli = 48
For li = 2 To nli
    Sheets(2).Cells(li, "A").Formula = "='Nomenclature brute'!A" & li 'Recopie du niveau hiérarchique"
    Sheets(2).Cells(li, "B").FormulaLocal = "=SOMMEPROD((NBCAR(A" & li & ")-NBCAR(SUBSTITUE(A" & li & ";""."";))))+1" 'Transformation du niveau hierarchique en niveau standard
    'Sheets(2).Cells(li, "B").Formula = "=SUMPRODUCT((LEN(A" & li & ")-LEN(SUBSTITUTE(A" & li & ";""."";))))+1" 'Transformation du niveau hierarchique en niveau standard
    Sheets(2).Cells(li, "J").Formula = "=IF(OR('Nomenclature brute'!I" & li & "=""0"",'Nomenclature brute'!I" & li & " = """"),"""",'Nomenclature brute'!I" & li & ")" 'Recopie de l'indice si différent de 0"
    Sheets(2).Cells(li, "D").Formula = "=UPPER(CONCATENATE('" & "Nomenclature brute'!C" & li & "," & "J" & li & "))" 'Insertion du code article en majuscule
    Sheets(2).Cells(li, "E").Formula = "=UPPER('" & "Nomenclature brute'!D" & li & ")" 'Recopie de la désignation en majuscule
    Sheets(2).Cells(li, "G").FormulaLocal = "=SI('Nomenclature brute'!F" & li & "="""";'Nomenclature brute'!E" & li & ";'Nomenclature brute'!E" & li & "*'Nomenclature brute'!F" & li & ")" 'Recopie de la quantité"
    'Sheets(2).Cells(li, "G").Formula = "=IF('Nomenclature brute'!F" & li & "="""";'Nomenclature brute'!E" & li & ";'Nomenclature brute'!E" & li & "*'Nomenclature brute'!F" & li & ")" 'Recopie de la quantité"
    Sheets(2).Cells(li, "H").FormulaLocal = "=RECHERCHEV('Nomenclature brute'!G" & li & ";Types_article;2;FAUX)" 'Conversion code de type article
    'Sheets(2).Cells(li, "H").Formula = "=VLOOKUP('Nomenclature brute'!G" & li & ";Types_article;2;FALSE)" 'Conversion code de type article
 
 
 Next li
 
End Sub

Je ne comprend pas pourquoi elle ne fonctionne pas!!8O

Merci encore pour les conseils.

Bonjour JohannCOU,

Citation:

Envoyé par JohannCOU

je vais tout passer en formulalocal pour ne plus avoir de problème.

Je pense effectivement que c'est beaucoup plus facile à réaliser car tu avais des ; qui en anglais sont des ,

Comme je t'avais dit tu peux supprimer ta boucle, ce qui sera plus rapide et plus facile à écrire car il n'y a plus besoin de variable.

Code:

1
2
3
4
5
6
7
8
nli = 48
    Sheets(2).Cells(2, "A").Resize(nli, 1).Formula = "='Nomenclature brute'!A2"  'Recopie du niveau hiérarchique"
    Sheets(2).Cells(2, "B").Resize(nli, 1).FormulaLocal = "=SOMMEPROD((NBCAR(A2)-NBCAR(SUBSTITUE(A2;""."";))))+1" 'Transformation du niveau hierarchique en niveau standard
    Sheets(2).Cells(2, "J").Resize(nli, 1).Formula = "=IF(OR('Nomenclature brute'!I2=""0"",'Nomenclature brute'!I2 = """"),"""",'Nomenclature brute'!I2)" 'Recopie de l'indice si différent de 0"
    Sheets(2).Cells(2, "D").Resize(nli, 1).Formula = "=UPPER(CONCATENATE('" & "Nomenclature brute'!C2,J2))" 'Insertion du code article en majuscule
    Sheets(2).Cells(2, "E").Resize(nli, 1).Formula = "=UPPER('" & "Nomenclature brute'!D2)" 'Recopie de la désignation en majuscule
    Sheets(2).Cells(2, "G").Resize(nli, 1).FormulaLocal = "=SI('Nomenclature brute'!F2="""";'Nomenclature brute'!E2;'Nomenclature brute'!E2*'Nomenclature brute'!F2)"  'Recopie de la quantité"
    Sheets(2).Cells(2, "H").Resize(nli, 1).FormulaLocal = "=RECHERCHEV(G2;Types_article;2;FAUX)" 'Conversion code de type article

27/02/2019, 10h34
JohannCOU

RE

Merci beaucoup anasecu,

Ah!! je ne savais pas que les ";" passait en "," en anglais.:roll:

Effectivement, cela fonctionne mieux et le Resize va m'être utile.

Merci et bonne journée à tous.