Insérer/Créer une formule en vba dans une cellule

Version imprimable

Bonjour,

J'essai d'insérer dans une cellule une formule construire entièrement à la main. Je dois la construire à la main car le nombre de ligne de ma feuille general_report peux-être variable. (Elle provient d'un export de

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub writeFormula()
    Dim strFormula As String
    Dim row As Long
    row = lastRow("general_report")
    strFormula = "=COUNTIFS(general_report!$BH$5:$BH$" & row & ";""S0 - Blocking"";general_report!$Q$5:$Q$" & row & ";""*""&DefectBySeverityAndSubSystem!B3&""*"";general_report!$E$5:$E$" & row & ";""<>Canceled"";general_report!$E$5:$E$" & row & ";""<>Resolved"";general_report!$E$5:$E$" & row & ";""<>UAT Resolved"";general_report!$E$5:$E$" & row & ";""<>Closed"")"
       On Error GoTo errorHandler
 
        Debug.Print strFormula
        Debug.Print "=COUNTIFS(general_report!$BH$5:$BH$" & row & ";""S0 - Blocking"";general_report!$Q$5:$Q$" & row & ";""*""&DefectBySeverityAndSubSystem!B3&""*"";general_report!$E$5:$E$" & row & ";""<>Canceled"";general_report!$E$5:$E$" & row & ";""<>Resolved"";general_report!$E$5:$E$" & row & ";""<>UAT Resolved"";general_report!$E$5:$E$" & row & ";""<>Closed"")"
        Worksheets("DefectBySeverityAndSubSystem").Activate
        'range("D11").Value = strFormula
        'range("D11").Formula = strFormula
        'range("D11").FormulaR1C1 = strFormula
        'range("D11").Value = "=COUNTIFS(general_report!$BH$5:$BH$" & row & ";""S0 - Blocking"";general_report!$Q$5:$Q$" & row & ";""*""&DefectBySeverityAndSubSystem!B3&""*"";general_report!$E$5:$E$" & row & ";""<>Canceled"";general_report!$E$5:$E$" & row & ";""<>Resolved"";general_report!$E$5:$E$" & row & ";""<>UAT Resolved"";general_report!$E$5:$E$" & row & ";""<>Closed"")"
        'range("D11").Formula = "=COUNTIFS(general_report!$BH$5:$BH$" & row & ";""S0 - Blocking"";general_report!$Q$5:$Q$" & row & ";""*""&DefectBySeverityAndSubSystem!B3&""*"";general_report!$E$5:$E$" & row & ";""<>Canceled"";general_report!$E$5:$E$" & row & ";""<>Resolved"";general_report!$E$5:$E$" & row & ";""<>UAT Resolved"";general_report!$E$5:$E$" & row & ";""<>Closed"")"
        'range("D11").FormulaR1C1 = "=COUNTIFS(general_report!$BH$5:$BH$" & row & ";""S0 - Blocking"";general_report!$Q$5:$Q$" & row & ";""*""&DefectBySeverityAndSubSystem!B3&""*"";general_report!$E$5:$E$" & row & ";""<>Canceled"";general_report!$E$5:$E$" & row & ";""<>Resolved"";general_report!$E$5:$E$" & row & ";""<>UAT Resolved"";general_report!$E$5:$E$" & row & ";""<>Closed"")"
        'ActiveWorkbook.Names.Add Name:="myFormula", RefersToR1C1:= _
        '   "=COUNTIFS(general_report!$BH$5:$BH$" & row & ";""S0 - Blocking"";general_report!$Q$5:$Q$" & row & ";""*""&DefectBySeverityAndSubSystem!B3&""*"";general_report!$E$5:$E$" & row & ";""<>Canceled"";general_report!$E$5:$E$" & row & ";""<>Resolved"";general_report!$E$5:$E$" & row & ";""<>UAT Resolved"";general_report!$E$5:$E$" & row & ";""<>Closed"")"
        '   ActiveWorkbook.Names("myFormula").Comment = ""
errorHandler:
    MsgBox Err.Number & vbLf & Err.Description
 
End Sub
 
Function lastRow(nameSheet As String) As Long
    Dim sht As Worksheet
 
    Set sht = ThisWorkbook.Worksheets(nameSheet)
 
    lastRow = sht.Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).row
 
End Function

J'ai une erreur 1004.

Quand je prends la formule écrite dans la fenêtre "Immediate" et que je la copie/colle dans une cellule elle marche parfaitement bien.

J'ai essayé plusieurs choses, (en commentaire) notament de créer à la volé une formule pour ensuite l'affecter à une cellule (le code en commentaire) mais là aussi en vain.

Ma formule serait-elle trop compliquée pour être insérer en vba ?

Merci pour votre aide.

Shiyatsu.

07/10/2015, 10h25
Marc-L

Bonjour !

Comparer de nouveau la formule dans la fenêtre avec chaque caractère de la formule VBA !

Sinon un Debug.Print de la formule VBA permet aussi de comparer avec la source
rendant ainsi plus facile le dépistage de sa boulette …

Si les deux sont strictement équivalentes, c'est alors un problème de conception
comme un non respect du modèle objet, par exemple la feuille active n'étant pas celle attendue …

_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse

1 pièce(s) jointe(s)

Bonjour,

Merci pour votre réponse,

J'ai déjà comparé les Debug.print, et lorsque que je colle le résultat de mon Debug.print, la formule marche très bien et les deux sont bel et bien identique.

Je pense que le problème est ailleurs.

Pour m'assurer que ma synthaxe est correct, j'ai utilise le "Reccord Macro" de excel avec une formule simplifié.

J'ai donc fait ctrl+F3, et j'ai donc collé la formule suivante : =COUNTIFS(general_report!$BH$5:$BH$74;"S0 - Blocking")

J'ai obtenu alors le code suivant :
Code:

1 2 3 4 ActiveWorkbook.Names.Add Name:="recordMarco", RefersToR1C1:= _ "=COUNTIFS(general_report!R5C60:R74C60,""S0 - Blocking"")" ActiveWorkbook.Names("recordMarco").Comment = ""
Ma première question, savez-vous pourquoi il a changé mes $BH$5:$BH$74 en R5C60:R74C60 ?

Et quand je test donc le code suivant :
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub writeFormula() On Error GoTo errorHandler range("D13").Select ActiveWorkbook.Names.Add Name:="recordMarco", RefersToR1C1:= _ "=COUNTIFS(general_report!$BH$5:$BH$74;""S0 - Blocking"")" ActiveWorkbook.Names("recordMarco").Comment = "" errorHandler: MsgBox Err.Number & vbLf & Err.Description End Sub
J'ai une fenêtre d'erreur contenant ceci :

Pièce jointe 189596

Merci,

Shiyatsu.

Edit : Je viens d'essayé le "Record macro" en collant ma formule : =COUNTIFS(general_report!$BH$5:$BH$74;"S0 - Blocking") dans une cellule, j'ai obtenu la même chose :
Code:

1 2 3 4 ActiveCell.FormulaR1C1 = _ "=COUNTIFS(general_report!R5C60:R74C60,""S0 - Blocking"")" range("E9").Select

07/10/2015, 11h22
Marc-L

Remettre l'affichage des colonnes en mode lettre peut-être … (Sinon c'est l'Enregistreur qui préfère le mode numérique.)

Entrer manuellement la formule de calculs dans une cellule d'une feuille de calculs.
Une fois fonctionnelle, côté VBE dans la fenêtre Immediate valider ? activecell.formula :
c'est donc avec cette formule qu'il faut comparer …
07/10/2015, 11h53
Shiyatsu
Re-bonjour,

Je viens de trouver la solution donc je vous là partage.

Et vous pourrez je pense m'apporter des petites precisions sur ce que je pense avoir compris.

Quand l'on souhaite écrire une formule en vba qui contient des références de cellule, la synthaxe est la suivante :

RNuméroDeLaLigneCNuméroDeLaColonne

En d'autre terme, pour écrire en vba :
Code:

1 2 3 =COUNTIFS(general_report!$A$5:$A$74;"S0 - Blocking") =COUNTIFS(general_report!R5C0:R74C0;"S0 - Blocking")
R5C0 correspond à ligne 5 de la colonne A
R74C0 correspond à ligne 74 de la colonne A

Et pour les cellules qui ne sont pas des références

Si je veux dire que ma cellule B5 est égale à la cellule A3 (Donc ça se résume à écrire =A3 dans la cellule B5)
En gros il faut lui dire l'endroit où la cellule ce trouve pas rapport à la cellule active.
Pour passer de B5 à A3 je dois remonter d'une ligne, puis allez vers la gauche de 3 cases soit :

Code:

ActiveCell.FormulaR1C1 = "=R[-1]C[-3]"

J'ai dois tout simplement changer la construction de mes formules pour qu'il face en sorte de changer mes cellules noté en brut (avec lettres et chiffres) avec cette synthaxe.

Je sais pas si j'ai été bien claire dans mon explication^^

Merci encore pour votre aide.

Shiyatsu.
07/10/2015, 13h46
Marc-L

Je passe par la propriété Formula comme expliqué précédemment …