Boucles avec des lettres dans une formule injecté à des cellules avec VBA

Le 30/01/2019 à 17:05

Bonjour,

Voici mon code VBA qui fonctionne, là n'est pas le soucis !

Je veux simplement trouver une solution pour ne pas écrire 1000 lignes de code :

Private Sub CommandButton1_Click()

Dim variable_1 As Integer
variable_1 = "298"            

Dim variable_2 As String
variable_2 = "295;N6)"         

Worksheets("PLANNINH HxJ").Range("O" & variable_1).FormulaLocal = "=cumul_couleur(O6:O" & variable_2
Worksheets("PLANNINH HxJ").Range("P" & variable_1).FormulaLocal = "=cumul_couleur(P6:P" & variable_2
Worksheets("PLANNINH HxJ").Range("Q" & variable_1).FormulaLocal = "=cumul_couleur(Q6:Q" & variable_2

J'aimerais répéter cette formule de la colonne O à la colonne ZZ, y a t-il un moyen, ou je me fais les 1000 lignes ?

Merci de votre aide !

Le 30/01/2019 à 17:23

Bonjour BabaN,

Une idée :

Sub remplissage()

Dim variable_1 As Integer
variable_1 = "298"

Dim variable_2 As String
variable_2 = "295;N6)"

For Each cell In Range("O" & variable_1 & ":ZZ" & variable_1)

cell.FormulaLocal = "=cumul_couleur(O6:O" & variable_2

Next

End Sub

Bonne fin d'après-midi

Le 30/01/2019 à 17:25

Bonjour,

Tes variables ne sont pas vraiment variables, alors pourquoi t'embêter avec VBA ?

Sinon, à la place de "Range("A" & i)", tu peux utiliser "Cells(i, 1)", qui permet l'utilisation d'un indice de ligne et d'un indice de colonne.

L'indice de colonne peux être géré par une variable au sein d'une boucle, du style :

With Worksheets("MaFeuille")
    For Col = 1 To 1000
        .Cells(1, Col).FormulaLocal = "=MA_FORMULE_MAGIQUE()"
    Next Col
End With
Le 31/01/2019 à 09:46
SlimShady69 a écrit :

Bonjour BabaN,

Une idée :

Sub remplissage()

Dim variable_1 As Integer
variable_1 = "298"

Dim variable_2 As String
variable_2 = "295;N6)"

For Each cell In Range("O" & variable_1 & ":ZZ" & variable_1)

cell.FormulaLocal = "=cumul_couleur(O6:O" & variable_2

Next

End Sub

Bonne fin d'après-midi

Petit soucis avec ce code, c'est que la formule "=cumul_couleur(O6:O etc doit aussi changer jusqu'à ZZ !!

Merci de ta réponse tout de même

Le 31/01/2019 à 09:49
Pedro22 a écrit :

Bonjour,

Tes variables ne sont pas vraiment variables, alors pourquoi t'embêter avec VBA ?

Sinon, à la place de "Range("A" & i)", tu peux utiliser "Cells(i, 1)", qui permet l'utilisation d'un indice de ligne et d'un indice de colonne.

L'indice de colonne peux être géré par une variable au sein d'une boucle, du style :

With Worksheets("MaFeuille")
    For Col = 1 To 1000
        .Cells(1, Col).FormulaLocal = "=MA_FORMULE_MAGIQUE()"
    Next Col
End With

En faites j'ai bien deux variables, comme j'ajoute ou supprime des lignes dans mon fichier à ma convenance, ces deux variables vont changer donc je pensais les modifier à chaque fois que cela arrivait, je ne sais pas si je me fais comprendre...

Parce que j'ai essayé de mettre ma variable_1 = Range("D1") qui est le chiffre 298 mais ça ne marche pas ! Il me met erreur 1004 d'exécution

Le 31/01/2019 à 09:52
BabaN a écrit :

Parce que j'ai essayé de mettre ma variable_1 = Range("D1") qui est le chiffre 298 mais ça ne marche pas ! Il me met erreur 1004 d'exécution

Bonjour,

A quel endroit du code ? Peux-tu transmettre ce que tu as produit ?

PS : cumul_couleur, c'est une fonction personnalisée ? As tu besoin d'inscrire la formule dans la cellule, ou est ce que mettre directement le résultat à chaque exécution de la macro conviendrait ?

Le 31/01/2019 à 10:19

[/quote]Bonjour,

A quel endroit du code ? Peux-tu transmettre ce que tu as produit ?

PS : cumul_couleur, c'est une fonction personnalisée ? As tu besoin d'inscrire la formule dans la cellule, ou est ce que mettre directement le résultat à chaque exécution de la macro conviendrait ?

[/quote]

Le cumul_couleur est bien une fonction personnalisé que j'ai crée avec VBA aussi, il a besoin d'une cellule de plage en premier lieu, ensuite d'une référence, il me fait la somme des chiffres qui ont la même couleur ensuite. J'ai donc bien besoin d'inscrire la formule !

Ensuite voilà mon nouveau code que j'ai modifié ce matin :

'Définition des variables

Dim variable_1 As Integer
variable_1 = Range("B1").Value            'variable me donnant un chiffre correspondant au numéro de ma ligne

Dim variable_2 As Integer
variable_2 = Range("B2").Value             'variable me donnant un chiffre correspondant au numéro de ma ligne

'Calcul budget heures théoriques
Worksheets("PLANNINH HxJ").Range("P" & variable_1).FormulaLocal = "=cumul_couleur(P6:P" & variable_2 & ";N6)"
Worksheets("PLANNINH HxJ").Range("Q" & variable_1).FormulaLocal = "=cumul_couleur(Q6:Q" & variable_2 & ";N6)"

ça marche complètement sur mon fichier ! Mais si je comprends bien avec Cells je pourrais écrire :

Worksheets("PLANNINH HxJ").Cells(Range("B1").value;P).FormulaLocal etc ?

Comment je peux faire marcher la boucle avec ça ? en remplaçant P par Col comme tu me l'avais écrit ?

Merci en tout cas !

Le 31/01/2019 à 10:36
BabaN a écrit :

Le cumul_couleur est bien une fonction personnalisé que j'ai crée avec VBA aussi, il a besoin d'une cellule de plage en premier lieu, ensuite d'une référence, il me fait la somme des chiffres qui ont la même couleur ensuite. J'ai donc bien besoin d'inscrire la formule !

Pas besoin d'inscrire la formule dans la cellule pour obtenir un résultat !

Par exemple, dans excel tu souhaites faire =SOMME(A1:A10). Tu peux faire dans VBA :

Range("Truc") = Application.Sum(Range("A1:10"))

Ceci inscrira directement le résultat de la somme en valeur, mais si tu modifies une valeur de la plage A1:A10, le résultat ne sera pas actualisé à moins de ré-exécuter la macro.

BabaN a écrit :

Worksheets("PLANNINH HxJ").Cells(Range("B1").value;P).FormulaLocal etc ?

Comment je peux faire marcher la boucle avec ça ? en remplaçant P par Col comme tu me l'avais écrit ?

Je ne comprend pas la question, dans mon exemple tu as bien une boucle sur la variable Col... Alors qu'est ce qui te pose problème ?

Aussi, les indices de ligne et colonne dans "cells" ne sont pas séparés par ";" mais pas ",". D'une manière générale, ";" ne s'utilise pas dans VBA.

Le 31/01/2019 à 10:43

Bonjour,

Avec la méthode de Pedro,

comme ça peut être :

Sub test()

Dim variable_1 As Integer
variable_1 = "298"

Dim variable_2 As String
variable_2 = "295;N6)"

Dim NomCol As String
With Worksheets(1)
    For Col = 1 To 702
    NomCol = Split(Cells(1, Col).Address, "$")(1)
        .Cells(variable_1, Col).FormulaLocal = "=cumul_couleur(" & NomCol & "6:" & NomCol & variable_2
    Next Col
End With
End Sub
Le 31/01/2019 à 10:53
SlimShady69 a écrit :
Sub test()

Dim variable_1 As Integer
variable_1 = "298"

Dim variable_2 As String
variable_2 = "295;N6)"

Dim NomCol As String
With Worksheets(1)
    For Col = 1 To 702
    NomCol = Split(Cells(1, Col).Address, "$")(1)
        .Cells(variable_1, Col).FormulaLocal = "=cumul_couleur(" & NomCol & "6:" & NomCol & variable_2
    Next Col
End With
End Sub

C'est une possibilité, par contre concernant tes 2 variables :

- On préférera toujours grouper les déclarations de variable en-tête de procédure :

Dim variable_1 As Integer, variable_2 As Integer
  • Leur donner un nom explicite (variable_1 --> c'est quoi ?)
  • Déclarer un type adéquate, par exemple "298" c'est du texte, pas un nombre entier, sinon il faut écrire :
variable_1 = 298

- Pour variable_2, garde plutôt le nombre entier et ajoute le reste plus tard :

.Cells(variable_1, Col).FormulaLocal = "=cumul_couleur(" & NomCol & "6:" & NomCol & variable_2 & ";N6)" 'en fin d'instruction de chaque formule (ce sera plus clair)

- Si variable_2 = variable_1 + 3 dans tous les cas, alors n'utilises qu'une seule variable

Le 31/01/2019 à 10:59

@Pedro, je suis d'accord avec toi pour la variable_2, moi aussi ça m'a perturbé mais je voulais gardé ce qu'avait fait BabaN

Et effectivement le type de variable_1 m'avait échappé.

@BabaN, en attente de ton retour

Le 31/01/2019 à 11:06
SlimShady69 a écrit :

@Pedro, je suis d'accord avec toi pour la variable_2, moi aussi ça m'a perturbé mais je voulais gardé ce qu'avait fait BabaN

Et effectivement le type de variable_1 m'avait échappé.

@BabaN, en attente de ton retour

Pas de soucis, les commentaires lui sont destinés en priorité puisque effectivement cette partie a été reprise telle quelle dans son code.

Le 31/01/2019 à 11:43
Dim variable_1 As Integer                                'variable correspondant au numéro de ligne du Budget théorique
variable_1 = Range("B1").Value

Dim variable_2 As Integer                                'variable correspondant au numéro de de la dernière ligne calculée
variable_2 = Range("B2").Value

'Calcul budget heures théoriques
Dim NomCol As String
With Worksheets("PLANNINH HxJ")
    For col = 16 To 702
    NomCol = Split(Cells(1, col).Address, "$")(1)
        .Cells(variable_1, col).FormulaLocal = "=cumul_couleur(" & NomCol & "6:" & NomCol & variable_2 & ";N6)"
    Next col
End With
End Sub

Voici le code final qui marche mais qui met environ 7 minutes à tout calculer , je me contente de ça pour le moment, en tout cas merci beaucoup à vous deux !!

Concernant la variable 2, en effet elle dépend de ma variable 1, j'ai juste un peu peur de tout emmêler vu que je suis débutante encore, donc je décortique chaque calcul !

Pedro, je vais essayer de mettre le résultat directement plutôt que la formule, je gagnerais peut être quelques minutes d'ailleurs

Le 31/01/2019 à 11:57

Proposition 1 (code plus lisible et quelques modif pour accélérer l'éxécution) :

Sub Test()

Dim LigBudget As Integer, DerLig As Integer, Col As Integer, NomCol As String 'Déclaration des variables

Application.ScreenUpdating = False 'Désactive l'affichage le temps de l’exécution (rapidité +)
Application.Calculation = xlCalculationManual 'Désactive le recalcule auto des formules Excel à chaque modification (rapidité +)

LigBudget = Range("B1").Value
DerLig = LigBudget + 3

'Calcul budget heures théoriques
With Worksheets("PLANNINH HxJ")
    For Col = 16 To 702
        NomCol = Split(Cells(1, Col).Address, "$")(1)
        .Cells(LigBudget, col).FormulaLocal = "=cumul_couleur(" & NomCol & "6:" & NomCol & DerLig & ";N6)"
    Next Col
End With

Application.Calculation = xlCalculationAutomatic 

End Sub

Proposition 2 (proposition 1 + écriture du résultat de la formule "en dur") :

Sub Test()

Dim LigBudget As Integer, DerLig As Integer, Col As Integer, MaPlage As Range 'Déclaration des variables

Application.ScreenUpdating = False 'Désactive l'affichage le temps de l’exécution (rapidité +)
Application.Calculation = xlCalculationManual 'Désactive le recalcule auto des formules Excel à chaque modification (rapidité +)

LigBudget = Range("B1").Value
DerLig = LigBudget + 3

'Calcul budget heures théoriques
With Worksheets("PLANNINH HxJ")
    For Col = 16 To 702
        Set MaPlage = .Range(.Cells(6, Col), .Cells(DerLig, Col))
        .Cells(LigBudget, Col)= Application.cumul_couleur(MaPlage,.Range("N6"))
    Next Col
End With

Application.Calculation = xlCalculationAutomatic 

End Sub
Le 31/01/2019 à 13:13

Waaaw !! Pedro22 mais c'est génial !! J'espère être aussi qualifiée un jour !!

Merci pour l'aide, tes dernières propositions marchent parfaitement, l'une comme l'autre

Le 31/01/2019 à 13:51
BabaN a écrit :

Waaaw !! Pedro22 mais c'est génial !! J'espère être aussi qualifiée un jour !!

Merci pour l'aide, tes dernières propositions marchent parfaitement, l'une comme l'autre

Quand tu seras davantage qualifié, tu te rendras vite compte que je ne suis pas tant !

Au plaisir d'avoir apporté mon aide.

Le 07/02/2019 à 16:59

Bonjour !

J'essaye de passer au niveau supérieur en essayant cette fois d'appliquer directement le résultat au lieu de la formule,

donc cette fois j'utilise ta deuxième proposition Pedro22, et il y a un bug. "Erreur d'exécution 438"

En lisant à droite à gauche, j'ai vu que c'était un problème de formulation, j'ai essayé d'enlever les points par exemple mais ce n'est pas ça.

Est-ce lié au fait que j'ai crée ma fonction "Cumul_couleur" ?

Merci !!

Le 08/02/2019 à 15:05
BabaN a écrit :

Bonjour !

J'essaye de passer au niveau supérieur en essayant cette fois d'appliquer directement le résultat au lieu de la formule,

donc cette fois j'utilise ta deuxième proposition Pedro22, et il y a un bug. "Erreur d'exécution 438"

En lisant à droite à gauche, j'ai vu que c'était un problème de formulation, j'ai essayé d'enlever les points par exemple mais ce n'est pas ça.

Est-ce lié au fait que j'ai crée ma fonction "Cumul_couleur" ?

Merci !!

Bonjour,

Sans le code utilisé ni le fichier sous les yeux, difficile de te dire d'où vient le problème !

Le 08/02/2019 à 15:30

Le fichier est trop volumineux pour que je le mette ici (je t'ai envoyé un mp pour ça), mais c'est quasiment ton code que j'ai recopié (proposition 2) :

Private Sub CommandButton1_Click()

'Renommer le 1er bouton
CommandButton1.Caption = "Calcul du budget"

Dim LigBudget As Integer, DerLig As Integer, SerLig As Integer, Col As Integer, NomCol As String, MaPlage As Range 'Déclaration des variables

Application.ScreenUpdating = False 'Désactive l'affichage le temps de l’exécution (rapidité +)
Application.Calculation = xlCalculationManual 'Désactive le recalcule auto des formules Excel à chaque modification (rapidité +)

LigBudget = Range("B1").Value
DerLig = LigBudget - 3
SerLig = LigBudget + 2

'Calcul budget heures théoriques
With Worksheets("PLANNINH HxJ")
    For Col = 16 To 702
        Set MaPlage = .Range(Cells(6, Col), Cells(DerLig, Col))
        .Cells(LigBudget, Col) = Application.cumul_couleur(MaPlage, Range("N6"))
    Next Col
End With

With Worksheets("PLANNINH HxJ")
    For Col = 16 To 702
        Set MaPlage = .Range(Cells(6, Col), Cells(DerLig, Col))
        .Cells(SerLig, Col) = Application.cumul_couleur(MaPlage, Range("G1"))
    Next Col
End With

Application.Calculation = xlCalculationAutomatic

End Sub
Rechercher des sujets similaires à "boucles lettres formule injecte vba"