Discussion :

[Shiyatsu]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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

[Shiyatsu]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :



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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

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

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Marc-L]

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