Formule ne fonctionne après création par macro

Signaler
-
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
-
Bonjour,
Avec VB, je modifie un fichier excel et notamment je réactualise des cellules avec une formule (fonction en VB) pour qu'elles s'appliquent sur les nouvelles lignes du fichier.
Dans le fichier de départ la formule est par exemple pour la cellule D32 et fonctionne très bien :
=CountByColor(D$7:D$31;$O$2)
En VB, je fais :

lettre = "D"
mem_debut = 7
compteur3 = 10
ActiveSheet.Cells(11, 4).FormulaLocal = "=CountByColor(" + CStr(lettre) + "$" + CStr(mem_debut) + ":" + CStr(lettre) + "$" + CStr(compteur3) + ";$O$2)"
J'obtiens :
=CountByColor(D$7:D$10;$O$2)
Sauf que le résultat est #valeur! quoique je fasse.

Est-ce que quelqu'un aurait une idée sur ce qui ne va pas ?
Merci d'avance

12 réponses

Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bonjour;
1) et où est le code de ta CountByColor ? on ne le voit pas
2) Cstr(lettre) est superfétatoire puisque lettre est déjà un string
Messages postés
7
Date d'inscription
lundi 29 octobre 2007
Statut
Membre
Dernière intervention
8 janvier 2014

Bonjour,
1) La fonction est dans le module :
Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range
Dim TempCount As Long

TempCount = 0
For Each cl In InputRange.Cells
If cl.Interior.Color = ColorRange.Cells(1, 1).Interior.Color Then
TempCount = TempCount + 1
End If
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function


2) Ok pour Cstr

Merci...
--
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bonjour, cyrilou421,
ce n'est pas à toi, que j'ai demandé ce qu'avait écrit Cyril, mais à Cyril.
Rien ne me dit que la fonction écrite par Cyril est celle que toi, tu nous présentes !
Quelque-chose ne va pas ? M'échappe ? ou t'échappe ?
A méditer, donc ...
A Cyril donc : Quelle est TA réponse ? C'est TOI, le demandeur ...

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend
Ah ...
Je vois deux comparses, ici ...
Moi, c'est Cyrilala ...
Mon intervention ici juste pour dire que la fonction montrée par cyrilou421 n'est pas celle écrite par Cyril
Et moi, je suis un autre cyrilala ...
Tous les autres cyrilxxxxx apparaissant dans cette discussion sont des imposteurs.
Messages postés
7
Date d'inscription
lundi 29 octobre 2007
Statut
Membre
Dernière intervention
8 janvier 2014

Désolé, c'est ma faute.
Cyril qui a posté le message initial et cyrilou421, c'est la même personne. J'avais oublié de m'identifier lorsque j'ai déposé ma demande. Et maintenant je le suis, donc quand je réponds ca apparait sous cyrilou421. Mais c'est bien la même personne et c'est bien ma fonction.
Désolé


--
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Bon ...
Comme je le devinais bien, ton problème n'était pas dans le code montré par ton tout premier message mais dans la fonction personnalisée elle-même :
1) Voilà comment l'écrire :
Public Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range
For Each cl In InputRange.Cells
If cl.Interior.Color = ColorRange.Interior.Color Then
CountByColor = CountByColor + 1
End If
Next cl
Set cl = Nothing
End Function
2) (et TRES IMPORTANT) : où l'écrire :
Dans un module standard (insertion module)
3) Il vaut mieux utiliser Formula, plutôt que FormulaLocal ===>>
lettre = "D"
mem_debut = 7
compteur3 = 10
ActiveSheet.Cells(11, 4).Formula = "=CountByColor(" & lettre & "$" & CStr(mem_debut) & ":" & lettre & "$" & CStr(compteur3) & ",$O$2)"
4) il va de soi que la cellule concernée doit être formatée en nombre

Messages postés
7
Date d'inscription
lundi 29 octobre 2007
Statut
Membre
Dernière intervention
8 janvier 2014

Bonsoir,
Merci pour tes réponses.
J'ai tout mis comme tu m'as dit.
Il y a bien la formule et il n'y a plus #valeur!

Le nouveau problème, c'est que si je change la couleur des cellules. Le résultat ne se calcule pas.
Il faut que je double-clique sur la case, et appuie sur entrer pour que le calcul se fasse.

Merci d'avance
Cyril

--
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
C'est parce-que Excel ne considère un changement de format ni comme élément de calcul, ni comme modification entraînant un évènement.
Tu n'as dans ce cas pas d'autre choix que d'utiliser l'évènement SelectionChange de ta feuille de calcul , ainsi, par exemple :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lettre = "D"
mem_debut = 7
compteur3 = 10
ActiveSheet.Cells(11, 4).Formula = "=CountByColor(" & lettre & "$" & CStr(mem_debut) & ":" & lettre & "$" & CStr(compteur3) & ",$O$2)"
End Sub
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Ou encore :
laisser tel quel ce que tu avais déjà écrit auparavant et forcer la main à Excel dans l'évènement SelectionChange. Ainsi :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub
L'inconvénient, pour chacune de ces deux solutions, est qu'Excel travaillera chaque fois que ru changeras de sélection et non uniquement lorsque nécessaire.
Je vais essayer une acrobatie pour limiter cette intervention aux seuls cas nécessaires.
A plus.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Tiens ===>>> je m'en sors avec des sueurs froides et un torticolis, mais voilà le mécanisme "va-et-vient" qui te permettra de ne faire calculer que lorsque nécessaire et pas à chaque changement de cellule ===>>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static titi As Range
If titi Is Nothing Then Set titi = Target
If (Not Application.Intersect(Target, Range("D2:D10")) Is Nothing) Or (Not Application.Intersect(Target, Range("O2")) Is Nothing) Then
Me.Calculate
ElseIf Application.Intersect(Target, Range("D2:D10")) Is Nothing Or (Application.Intersect(Target, Range("O2")) Is Nothing) Then
If (Not Application.Intersect(titi, Range("D2:D10")) Is Nothing) Or (Not Application.Intersect(titi, Range("O2")) Is Nothing) Then
Me.Calculate
End If
End If
Set titi = Target
End Sub
Je vais au dodo ...

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviend
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Attention : corriger ici :
ElseIf Application.Intersect(Target, Range("D2:D10")) Is Nothing And (Application.Intersect(Target, Range("O2")) Is Nothing) Then
ce n'est pas Or, qu'il fallait, mais And, bien sûr.
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
232
Et ?
Où en es-tu maintenant, de ce problème ?