Discussion :

[bmartin0903]

Bonjour à tous,

je débute sur VBA notamment sur les variables.
si un post antérieur peux m'aider dans ma démarche je suis également preneuse car je n'ai pas réussi à trouver quelque chose comme ceci.

Je souhaite faire une formule en VBA: Somme.si.ens sur mon onglet n-1 en cellule C18 et les valeurs prises pour cette formule sont sur le dernier onglet (variable en nom d'onglet)

Je vais essayé de mieux vous expliquer.

j'ai un onglet synthèse nommé "évolution créance" avec les années (par exemple 2020), en C18 je fait la somme des montants selon mon dernier onglet. Je note la formule à la main ou par copier coller en changeant ma donnée "FAC clients au 31072023" pour avoir celle du jour : =SOMME.SI.ENS('FAC clients au 31072023'!$L:$L;'FAC clients au 31072023'!$D:$D;">=01/01/2020";'FAC clients au 31072023'!$D:$D;"<=31/12/2020")

j'ai essayé cette formule en VBA mais je ne trouve pas comment indiquer ma variable "FAC clients au 31072023" qui est le nom de l'onglet variable en fonction du jour d'édition (VBA aussi).

Code :

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

1
2
3
4
Dim derniere As Variant
Set derniere = Sheets(Sheets.Count)
Range("C18").FormulaR1C1 = _
        "=SUMIFS(derniere C[10],derniere C[2],"">=01/01/2020"",derniere C[2],""<=31/12/2020"")"

et voilà ce que j'obtiens lors de la validation : =SOMME.SI.ENS(derniere M:M;derniere E:E;">=01/01/2020";derniere E:E;"<=31/12/2020") au lieu d'obtenir : =SOMME.SI.ENS('FAC clients au 31072023'!$L:$L;'FAC clients au 31072023'!$D:$D;">=01/01/2020";'FAC clients au 31072023'!$D:$D;"<=31/12/2020")

Merci de me faire part de votre savoir pour que je puisse avancer dans le mien.
n'hésitez pas à revenir vers moi si mon post n'est pas clair et s'il faut que je mette un excel.

Merci à tous et bonne journée / vacances pour ceux qui le sont.

[Philippe Tulliez]

Bonjour,
Il y a plusieurs erreurs dans votre code.
La valeur que l'on doit passer à la propriété FormulaR1C1 est une chaîne de caractères. C'est à dire que vous devez passer le nom de la feuille suivi de l'apostrophe et il ne doit pas avoir de caractère espacé entre le nom de la feuille et l'adresse de la cellule.

Lorsque je dois écrire une formule dans une ou plusieurs cellules d'excel, j'utilise toujours des balises pour ce qui doit être paramétrable et je met le texte complet dans un modèle (ici la constante p), je remplace ensuite les balises par les valeurs réelles

Ma proposition

Code :

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

1
2
3
4
5
6
7
8
Sub t()
 Const p As String = "=SUMIFS(<sheet>!C[10],<sheet>!C[2],"">=01/01/2020"",<sheet>!C[2],""<=31/12/2020"")"
 Dim S As String ' Nom de la feuille
 Dim f As String ' Texte de la formule à passer à la propriété FormulaR1C1
 S = Sheets(Sheets.Count).Name
 f = Replace(p, "<sheet>", S)
 ActiveSheet.Range("C18").FormulaR1C1 = f
End Sub

Petites remarques

• Pour éviter des surprises désagréables, il est important de préciser la parentalité d'un objet. J'ai donc ajouté ActiveSheet à la dernière ligne de code (à modifier par le véritable nom de votre feuille soit Worksheets("nom") ou son CodeName
• Il n'est pas productif, d'utiliser les colonnes complètes pour effectuer des calculs ou des recherches. Privilégiez la référence aux plages réellement utilisées
• je ne peux que vous conseiller, l'utilisation des tableaux structurés
• Est-il judicieux, d'avoir autant de feuilles. Ne pouvez-vous pas regroupe l'ensemble de chiffres annuels ou mensuels dans une seule feuille ?
• A titre indicatif, vous pouvez utiliser la propriété Formula au lieu de FormulaR1C1, ce qui vous permet d'utiliser le style d'adresse A1 au lieu de R1C1

Voir les billets que j'ai écrit sur ce sujet ainsi que le tutoriel de Pierre Fauconnier

• Comment écrire une formule dans une plage de cellules en VBA ?
• A l’aide de VBA, comment écrire une formule plus complexe dans une cellule ?
• Excel VBA - La parentalité d’un objet
• Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)

[bmartin0903]

Bonjour,

Merci Philippe Tulliez pour votre retour qui correspond au changement de nom dans la formule.
J'ai bien compris votre article sur la parentalité sur des valeurs fixes.

Merci pour vos conseils, aurai-je trop de variable?

Lors du changement de la valeur dans la formule selon la dernière feuille, le lien entre le nom à remplacer et la feuille du même nom ne se fait pas... il manque l'apostrophe de la feuille et si je vais dans la cellule pour valider, je dois ouvrir le fichier pour créer le lien mais cela ne fonctionne pas également (le but étant de ne pas avoir besoin de valider).

Aurai-je mal fait mes autres codes?

Comme déjà énoncé, mon dernier onglet change toutes les semaines de nom, c'est pourquoi j'ai besoin de cette variable.

MERCI BEAUCOUP BEAUCOUP

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
 
Sub creance()
'MAJ de l'onglet evolution créance
    Sheets("évolution créance").Select
    Sheets("évolution créance").Move After:=Sheets(Sheets.Count - 1)
 
' selectionner la derniere cellule vide A
    ActiveSheet.Range("A:A").End(xlDown).Select
    ActiveCell.Formula = "=TODAY()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
'selectionner la case C vide
    ActiveSheet.Range("C:C").End(xlDown).Select
 
'formule 2020 avec la dernière feuille    
Const p As String = "=SUMIFS(<sheet>!C[09],<sheet>!C[01],"">=01/01/2020"",<sheet>!C[01],""<=31/12/2020"")"
 Dim S As String ' Nom de la feuille
 Dim f As String ' Texte de la formule à passer à la propriété FormulaR1C1
 derniere = Sheets(Sheets.Count).Name
 f = Replace(p, "<sheet>", S)
 ActiveSheet.Range("C:C").End(xlDown).FormulaR1C1 = f
 
End Sub

[Philippe Tulliez]

Bonjour,

Lors du changement de la valeur dans la formule selon la dernière feuille, le lien entre le nom à remplacer et la feuille du même nom ne se fait pas... il manque l'apostrophe de la feuille et si je vais dans la cellule pour valider, je dois ouvrir le fichier pour créer le lien mais cela ne fonctionne pas également (le but étant de ne pas avoir besoin de valider).

Faites vous tourner à nouveau le programme lorsque vous ajouter une feuille ?
Il est possible bien entendu d'automatiser ce lancement par une procédure événementielle.

D'après votre illustration, vous travaillez avec un tableau structuré. Si c'est le cas, vous ne devez pas utiliser les références A1ou RC1 mais les références structurées.

Il serait intéressant également d'avoir une réflexion sur l'organisation de vos données car avoir une feuille par mois avec des données organisées de la même façon est un non sens.
Une seule feuille suffit amplement avec 10 années si vous les souhaitez et votre synthèse se limitera alors à un tableau croisé dynamique qui s'actualisera sans avoir besoins de changer des formules