Formule ne fonctionne après création par macro

Question

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 = 7compteur3 = 10ActiveSheet.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

ucfoutu · Answer

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

cyrilou421 · Answer

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...
--

ucfoutu · Answer

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

cyrilala · Answer

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

cyrilala · Answer

Et moi, je suis un autre cyrilala ...
Tous les autres cyrilxxxxx apparaissant dans cette discussion sont des imposteurs.

cyrilou421 · Answer

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é
 
 
--

ucfoutu · Answer

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 = NothingEnd 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 = 7compteur3 = 10ActiveSheet.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

cyrilou421 · Answer

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
 
--

ucfoutu · Answer

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

ucfoutu · Answer

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.CalculateEnd 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.

ucfoutu · Answer

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 = TargetEnd 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

ucfoutu · Answer

Et ? 
Où en es-tu maintenant, de ce problème ?

Formule ne fonctionne après création par macro

12 réponses

Votre réponse

Discussions similaires