Variable dans macro pour utiliser une fonction

L

Louis_Oc

XLDnaute Nouveau
Bonjour,
Le titre choisi n’est sans doute pas très clair…
En fait j’ai une fonction qui permet de calculer sur une plage de cellules les valeurs contenues dans des cases de même couleur (cf ci-dessous)

Je voudrais ensuite appeler cette fonction dans une macro qui attribuera à certaines cellules la fonction avec des paramètres bien définis.
Donc la ligne dans la macro aurait cette tête là :
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[-11]C:R[-3]C,""rouge"")"

Et c’est là que la bât blesse… j’aimerais que la plage définie dans la macro soit une variable : par exemple :
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[Zone 1]C:R[Zone 2]C,""rouge"")" où Zone 1 et Zone 2 sont des variables …
Et là j’obtiens dans la cellule NOM ?

Quel est le code pour mettre en variable ces infos, j’ai essayé avec Cells(x,y) mais cela ne fonctionne pas non plus…

Donc maintenant j’en réfère à la fonction HELP !!! :)
Merci à vous !!!!

Louis

La Fonction :

Function CouleurSomme(Zone As Range, Couleur As String)
Select Case Couleur
Case "rouge"
Couleur = 3
Case "vert"
Couleur = 35
Case "jaune"
Couleur = 36
End Select
For Each cell In Zone
If cell.Interior.ColorIndex = Couleur Then SCoul = SCoul + cell.Value
Next
CouleurSomme= SCoul
End Function
 
Trier par date Trier par votes
nolich

nolich

XLDnaute Occasionnel
Re : Variable dans macro pour utiliser une fonction

Bonjour Louis_Oc, bonjour à toutes et à tous :)

Louis_Oc à dit:
Donc la ligne dans la macro aurait cette tête là :
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[-11]C:R[-3]C,""rouge"")"

Et c’est là que la bât blesse… j’aimerais que la plage définie dans la macro soit une variable : par exemple :
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[Zone 1]C:R[Zone 2]C,""rouge"")" où Zone 1 et Zone 2 sont des variables …
Et là j’obtiens dans la cellule NOM ?
Cliquez pour agrandir...

Il est évident que tu as une erreur dans la formule que tu entres dans ActiveCell. Avant de te conseiller, je pense que nous (il n'y a pas que moi qui a lu ton message), aimerions connaître les contenus des variables "Zone 1" et "Zone 2".

Pour des variables, il est bien curieux qu'elles aient un espace dans le nom, idem si elles sont des plages ou cellules nommées :confused:

A te lire.

@+
 
Vote positif 0 Vote négatif
L

Louis_Oc

XLDnaute Nouveau
Re : Variable dans macro pour utiliser une fonction

merci pour ta réponse, Zone 1 ou Zone 2 c'etait juste pour l'écriture du message...
les 2 variables sont des valeurs pour couvrir la plage...
j'ai par exemple fait l'enregistrement automatique de la macro sur la feuille en appliquant la fonction et j'ai obtenu ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[-11]C:R[-3]C,""rouge"")" et j'aurais aimé que dans la macro je puisse mettre le -11 et le -3 en variable car la plage n'est jamais la même en fonction de la cellule dans laquelle j'exécute la fonction...
Je reconnais que mes messages ne sont pas très clairs et que j'ai un effort de sémantique "Excel" à faire... :)
Merci en tout cas de te pencher là dessus malgré le côté très indigeste de la lecture... :)
 
Vote positif 0 Vote négatif
nolich

nolich

XLDnaute Occasionnel
Re : Variable dans macro pour utiliser une fonction

Re, bonjour à toutes et à tous :)

Si j'ai bien compris, ce qui n'est pas certain, les variables que tu as appelées Zone 1 et Zone 2 ne sont que des nombres. C'est cela ?

Si c'est le cas, tu peux modifier ton code comme suis :

Code: 
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[" & "Zone 1" _
   & "]C:R[" & "Zone 2" & "]C,""rouge"")"

En espérant avoir compris :eek:

@+
 
Vote positif 0 Vote négatif
nolich

nolich

XLDnaute Occasionnel
Re : Variable dans macro pour utiliser une fonction

Re, bonsoir à toutes et à tous :)

Louis_Oc à dit:
Merci pour ta réponse mais quand je lance la macro avec cette ligne j'obtiens le message :
"Run time error'1004'
Application-defined or object-defined error....
Cliquez pour agrandir...

As-tu un fichier exemple sans données confidentielles ? Cela serait plus simple pour te proposer des formules ou des codes plus précis.

@+
 
Vote positif 0 Vote négatif
tototiti2008

tototiti2008

XLDnaute Barbatruc
Re : Variable dans macro pour utiliser une fonction

Bonjour à tous,

une proposition proche de celle de nolich, mais pas tout à fait identique :

Code: 
Dim Zone1 as long, Zone2 as Long
Zone1 = -12
Zone2 = -5
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[" & Zone1 _ 
   & "]C:R[" & Zone2 & "]C,""rouge"")"
 
Vote positif 0 Vote négatif
L

Louis_Oc

XLDnaute Nouveau
Re : Variable dans macro pour utiliser une fonction

tototiti2008 à dit:
Bonjour à tous,

une proposition proche de celle de nolich, mais pas tout à fait identique :

Code: 
Dim Zone1 as long, Zone2 as Long
Zone1 = -12
Zone2 = -5
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!CouleurSomme(R[" & Zone1 _ 
   & "]C:R[" & Zone2 & "]C,""rouge"")"
Cliquez pour agrandir...
et celle-ci fonctionne!!!!!
Vraiment un grand merci!!!!!!
première fois que je viens sur ce forum, et quelle réactivité!!! gentillesse, Excel n'est pratiqué que par des gentlemen... :)

et sans être trop gourmand, la formule est donnée en format R1C1, est-ce réalisable de la transcrire en format A1 (plus facile à gérer sur une macro)?
 
Vote positif 0 Vote négatif
tototiti2008

tototiti2008

XLDnaute Barbatruc
Re : Variable dans macro pour utiliser une fonction

Bonjour à tous,

c'est possible, mais pas toujours souhaité... le style R1C1 peut avoir des avantages car il est relatif à la position de la formule, mais passons :

Code: 
Dim Zone1 As Long, Zone2 As Long
'Pour une plage de A1 à B5
Zone1 = 1
Zone2 = 5
ActiveCell.Formula = "=PERSONAL.XLS!CouleurSomme(A" & Zone1 _
   & ":B" & Zone2 & ",""rouge"")"
 
Vote positif 0 Vote négatif
tototiti2008

tototiti2008

XLDnaute Barbatruc
Re : Variable dans macro pour utiliser une fonction

Re,

un truc du genre :

Code: 
'Donner des valeurs aux variables x1, y1, x2, y2
With Activesheet
ActiveCell.Formula = "=PERSONAL.XLS!CouleurSomme(" & .Cells(x1,y1).address _
   & ":" & .Cells(x2,y2).Address & ",""rouge"")"
End with

?
 
Vote positif 0 Vote négatif
tototiti2008

tototiti2008

XLDnaute Barbatruc
Re : Variable dans macro pour utiliser une fonction

Ou alors :

Code: 
'Donner des valeurs aux variables x1, y1, x2, y2
With Activesheet
ActiveCell.Formula = "=PERSONAL.XLS!CouleurSomme(" & .range(.Cells(x1,y1), .Cells(x2,y2)).Address & ",""rouge"")"
End with
 
Vote positif 0 Vote négatif
L

Louis_Oc

XLDnaute Nouveau
Re : Variable dans macro pour utiliser une fonction

tototiti2008 à dit:
Ou alors :

Code: 
'Donner des valeurs aux variables x1, y1, x2, y2
With Activesheet
ActiveCell.Formula = "=PERSONAL.XLS!CouleurSomme(" & .range(.Cells(x1,y1), .Cells(x2,y2)).Address & ",""rouge"")"
End with
Cliquez pour agrandir...
Bonjour Tototiti!
ça ne marche pas... j'ai l'erreur suivante Compile error : Invalid or unqualified reference
j'ai essayé avec les 2 propositions que tu as faite et j'ai la même erreur
"For m = 1 To MyValue
x1 = aa - 1
y1 = 5 + m
x2 = aa - nbrlignes
y2 = y1
Cells(aa, 5 + m).Select
'ActiveCell.Formula = "=PERSONAL.XLS!SomCool(" & .Range(.Cells(x1, y1), .Cells(x2, y2)).Address & ",""vert"")"
ActiveCell.Formula = "=PERSONAL.XLS!SomCool(" & .Cells(x1, y1).Address _
& ":" & .Cells(x2, y2).Address & ",""vert"")"
Next

Encore merci pour ton aide!!
Louis
 
Vote positif 0 Vote négatif
nolich

nolich

XLDnaute Occasionnel
Re : Variable dans macro pour utiliser une fonction

Re à tous les deux, bonjour à toutes et à tous :)

Louis_Oc à dit:
Bonjour Tototiti!
ça ne marche pas... j'ai l'erreur suivante Compile error : Invalid or unqualified reference
j'ai essayé avec les 2 propositions que tu as faite et j'ai la même erreur
"For m = 1 To MyValue
x1 = aa - 1
y1 = 5 + m
x2 = aa - nbrlignes
y2 = y1
Cells(aa, 5 + m).Select
'ActiveCell.Formula = "=PERSONAL.XLS!SomCool(" & .Range(.Cells(x1, y1), .Cells(x2, y2)).Address & ",""vert"")"
ActiveCell.Formula = "=PERSONAL.XLS!SomCool(" & .Cells(x1, y1).Address _
& ":" & .Cells(x2, y2).Address & ",""vert"")"
Next

Encore merci pour ton aide!!
Louis
Cliquez pour agrandir...

les instructions en Gras / Italique / bleu ne sont pas comprises par le compilateur. Il faut que tu fasses référence à une feuille précise, comme dans le code que tototiti2008 t'a proposé :

Code: 
'Donner des valeurs aux variables x1, y1, x2, y2
[B][COLOR="blue"]With[/COLOR] Activesheet[/B]
  ActiveCell.Formula = "=PERSONAL.XLS!CouleurSomme(" & .range(.Cells(x1,y1), .Cells(x2,y2)).Address & ",""rouge"")"
[B][COLOR="blue"]End with[/COLOR][/B]

Tu as simplement oublié les instructions With et End With de ce que j'en vois ;)

@+
 
Vote positif 0 Vote négatif

Discussions similaires

U
  • Question
XL 2021 incrementation de variable dans une boucle for next
Réponses
3
Affichages
203
Gégé-45550
Gégé-45550
N
  • Question
XL 2016 Recherche dans une macro pour changer la plage de données où importer
Réponses
5
Affichages
372
vgendron
vgendron
K
  • Question
Microsoft 365 Recherche valeur en fonction des 2 critères dans un tableau multi entrées
Réponses
3
Affichages
341
Khloe870
K
J
  • Résolu(e)
XL 2016 fonction array
2
Réponses
16
Affichages
485
sylvanu
sylvanu
P
  • Résolu(e)
XL 2016 Besoin d'ajouter une fonctionnalité d'envoi d'e-mails pour deux personnes dans un outil 5S
2
Réponses
15
Affichages
667
Pauuul35
P
M
  • Question
Microsoft 365 Ma fonction VBA s'exécute avec toutes mes Macros
Réponses
8
Affichages
417
MACx
M
T
  • Question
Microsoft 365 Comment donner des couleurs aux lignes d'une ListView excel VBA
2 3
Réponses
34
Affichages
3 K
TooFatBoy
TooFatBoy
Lu76Fer
  • Question
XL 2016 Démo pour positionner un UserForm ou ContextMenu sur la grille (Toute version)
10 11 12
Réponses
178
Affichages
5 K
TooFatBoy
TooFatBoy
I
  • Résolu(e)
Microsoft 365 VBA EXCEL Utilisation d'une variable avec données dans un tableau
Réponses
4
Affichages
682
mapomme
mapomme
Y
  • Résolu(e)
XL 2016 Ajouter la fonction AutoFilter à une macro qui fait l'import des données
Réponses
3
Affichages
251
YANNISE
Y

Membres actuellement en ligne

13 membres et 268 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 231
Messages
2 086 438
Membres
103 208
dernier inscrit
Natsu09