Problème Formule avec VBA

Version imprimable

17/05/2018, 11h08
Ihaben

Problème Formule avec VBA

Bonjour les amis,

J'ai réalisé la formule SOMME.SI.ENS sur excel qui a marché correctement, sauf qu'après je voulais passer par VBA pour faire le calcul.
Donc j'ai essayé de reformuler ma formule pour qu'elle puisse marcher sur VBA mais sa bug.
Voica ma formule sur Excel :

Code:

=SOMME.SI.ENS(Feuil1!$K:$K;Feuil1!$B:$B;Feuil4!$A$1;Feuil1!$C:$C;Feuil4!$A$2;Feuil1!$I:$I;"<>TTM RUNGIS";Feuil1!$I:$I;"<>*POMONA*";Feuil1!$F:$F;"=1";Feuil1!$K:$K;">0")+SOMME.SI.ENS(Feuil1!$K:$K;Feuil1!$B:$B;Feuil4!$A$1;Feuil1!$C:$C;Feuil4!$A$3;Feuil1!$I:$I;"<>TTM RUNGIS";Feuil1!$I:$I;"<>*POMONA*";Feuil1!$E:$E*";Feuil1!$F:$F;"=1";Feuil1!$K:$K;">0")

La partie que j'ai mis en rouge c'est le numéro de la semaine, qui sera saisi par l'utilisateur ==> NumSemaine sur mon code VBA

Et voici ma formule sur VBA :

Code:

ActiveSheet.Range("B6").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("k:k"), Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>*POMONA*"), Worksheets("SAGE").Range("F:F"), "NumSemaine", Worksheets("SAGE").Range("K:K"), ">0") + Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("k:k"), Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A3"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>*POMONA*"), Worksheets("SAGE").Range("F:F"), "NumSemaine", Worksheets("SAGE").Range("K:K"), ">0")

Merci d'avance.
17/05/2018, 13h50
joe.levrai

Bonjour,

ça bug, c'est vague ?

Message d'erreur ou résultat faux ?

Sommes-nous d'accord que ta variable n'est pas entre guillemets comme tu l'as ici écrits ? Car sinon c'est une chaine de caractère et pas le contenu de ladite variable

Bonjour,

En gros, voila ce que je veux réaliser.
Appuyer sur le bouton 1, une fenêtre s'affiche et demande le numéro de la semaine souhaité, puis il crée une copie de la feuil référence avec un nom S + numéro de la semaine choisie.
cela j'arrive à le réaliser.

Appuyer sur le bouton 2, pour effectuer des calculs, et sur cette partie que mon programme bug.

Mon macro je l'ai mit dans un module, et elle se présentera comme suit :

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
35
36
37
Sub Bouton_NewSheet()
'Déclaration variable NumSeamine
Dim NumSemaine As String
 
'Affichage d'une InputBox
NumSemaine = InputBox("Veuillez entrer un numéro de semaine", "Insertion d'une feuille vierge")
'La variable reçoit la valeur entrée dans l'InputBox
 
If NumSemaine <> "" Then
'Si la valeur est différente de "" on affiche le résultat
    Sheets("REF").Visible = True
    Sheets("REF").Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = "S" & NumSemaine
    ActiveSheet.Range("B1").Value = ActiveSheet.Name
    Sheets("REF").Visible = True
    MsgBox "L'analyse de la facturation a été pour la semaine " & NumSemaine
End If
End Sub
 
Sub Bouton_Calculer()
 
'Nbr De Colis SAGE
Range("B6").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("K:K"), Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("I:I"), "<>*POMONA*", Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), (">0"))
 
'Nbr De Livraison SAGE
Range("D6").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), ">0") + Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A3"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), ">0")
 
'Nbr De Livraison -20 Colis SAGE
Range("F6").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), "<=20", Worksheets("SAGE").Range("K:K"), ">0") + Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A3"), Worksheets("SAGE").Range("I:I"), Worksheets("TERMES").Range("<>A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), "<=20", Worksheets("SAGE").Range("K:K"), ">0")
 
'Nbr Retour Client SAGE
Range("B11").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), "<=0")
 
'Nbr Livraison POMONA SAGE
Range("B12").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("I:I"), "*POMONA*")
 
End Sub

17/05/2018, 14h11
joe.levrai

Citation:

Envoyé par joe.levrai

ça bug, c'est vague ?

Message d'erreur (quel message) ou résultat faux (quel résultat obtenu pour quel résultat attendu) ?
17/05/2018, 14h15
Ihaben

Désolé de ne pas avoir répondre à cette question.

Je reçoit le message suivant : "Erreur d'exécution '1004'"
17/05/2018, 14h19
joe.levrai

ceci

Code:

Worksheets("TERMES").Range("<>A4")

ne veut rien dire. Il y en a d'autres comme ça

Que voulais-tu écrire exactement ?
17/05/2018, 14h37
Ihaben

Sur la feuille TERMES et dans la cellule A4 j'ai un mot qui est enregistré.

Donc le calcul qui sera réalisé sur la colonne ne doit pas prendre en considération la valeur de A4. Donc faire la somme de toute la colonne sauf les cellules qui ont une valeur égal à celle de A4.
17/05/2018, 14h45
joe.levrai

Alors ça serait plutôt ainsi, si j'ai bien compris

Code:

"<>" & Worksheets("TERMES").Range("A4")

idem pour les autres portions de ta formule qui reposent sur ce principe
17/05/2018, 15h04
Ihaben

J'ai essayer d'appliquer ce que vous m'aviez proposé, mais maintenant je tombe sur l'erreur 9 "l'indice n'appartient pas à la sélection

Code:

Worksheets("REF").Range("B6").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("K:K"), Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("I:I"), "<>" & "*POMONA*", Worksheets("SAGE").Range("F:F"), "1", Worksheets("SAGE").Range("K:K"), (">0"))

Est-ce que l'ensemble de ces feuilles existent dans le même classeur, qui ici doit nécessairement être le classeur actif ?

je t'invite à bien préciser le classeur, par exemple

Code:

1
2
3
4
5
6
7
8
9
10
With Workbooks("MonClasseur.xlsm")
    .Worksheets("REF").Range("B6").Value = _
            Application.WorksheetFunction.SumIfs(.Worksheets("SAGE").Range("K:K"), _
                                                .Worksheets("SAGE").Range("B:B"), .Worksheets("TERMES").Range("A1"), _
                                                .Worksheets("SAGE").Range("C:C"), .Worksheets("TERMES").Range("A2"), _
                                                .Worksheets("SAGE").Range("I:I"), "<>" & .Worksheets("TERMES").Range("A4"), _
                                                .Worksheets("SAGE").Range("I:I"), "<>" & "*POMONA*", _
                                                .Worksheets("SAGE").Range("F:F"), "1", _
                                                .Worksheets("SAGE").Range("K:K"), ">0")
End With

Merci pour tes réponses Joe, vous m'aviez vraiment aidé.
J'ai réglé le soucis.

Sauf que maintenant il prend pas en considération le NumSemaine saisie par l'utilisateur. Dans les cases ou il y a les calculs il me met 0.
Par exemple si l'utilisateur saisie semaine : 1 donc NumSemaine = 1. Mais il la prends pas en considération.

Couleur Bleu sur le code :

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
35
36
37
38
Sub Bouton_NewSheet()
'Déclaration variable NumSeamine
Dim NumSemaine As String

'Affichage d'une InputBox
NumSemaine = InputBox("Veuillez entrer un numéro de semaine", "Insertion d'une feuille vierge")
'La variable reçoit la valeur entrée dans l'InputBox

If NumSemaine <> "" Then
'Si la valeur est différente de "" on affiche le résultat
    Sheets("REF").Visible = True
    Sheets("REF").Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = "S" & NumSemaine
    ActiveSheet.Range("B1").Value = ActiveSheet.Name
    Sheets("REF").Visible = True
    MsgBox "L'analyse de la facturation a été pour la semaine " & NumSemaine
End If
End Sub

Sub Bouton_Calculer()

'Nbr De Colis SAGE
Range("B6").Value = Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("K:K"), Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("I:I"), "<>" & "*POMONA*", Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), (">0")) + Application.WorksheetFunction.SumIfs(Worksheets("SAGE").Range("K:K"), Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A3"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("I:I"), "<>" & "*POMONA*", Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), (">0"))

'Nbr De Livraison SAGE
Range("D6").Value = Application.WorksheetFunction.CountIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), ">0") + Application.WorksheetFunction.CountIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A3"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), ">0")


'Nbr De Livraison -20 Colis SAGE
Range("F6").Value = Application.WorksheetFunction.CountIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A2"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), "<=20", Worksheets("SAGE").Range("K:K"), ">0") + Application.WorksheetFunction.CountIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("C:C"), Worksheets("TERMES").Range("A3"), Worksheets("SAGE").Range("I:I"), "<>" & Worksheets("TERMES").Range("A4"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), "<=20", Worksheets("SAGE").Range("K:K"), ">0")

'Nbr Retour Client SAGE
Range("B11").Value = Application.WorksheetFunction.CountIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("K:K"), "<=0")

'Nbr Livraison POMONA SAGE
Range("B12").Value = Application.WorksheetFunction.CountIfs(Worksheets("SAGE").Range("B:B"), Worksheets("TERMES").Range("A1"), Worksheets("SAGE").Range("F:F"), NumSemaine, Worksheets("SAGE").Range("I:I"), "*POMONA*")

End Sub

17/05/2018, 15h40
joe.levrai

La portée de ta variable NumSemaine n'est pas assez élevée, elle ne dépasse pas la procédure où elle est déclarée

place la déclaration tout en haut du module pour en augmenter la portée et qu'elle soit accessible depuis l'autre procédure
17/05/2018, 15h44
Ihaben

Merci beaucoup Joe, vous m'aviez sauver la vie sinon j'allais péter un câble.
17/05/2018, 15h58
joe.levrai

Super :mrgreen:

Bon, celui qui m'a mis -1, ça aurait sympa de m'indiquer en quoi ma proposition n'était pas pertinente, histoire d'apprendre d'une éventuelle erreur :roll:
18/05/2018, 14h47
Ihaben

Citation:

Envoyé par joe.levrai

Super :mrgreen:

Bon, celui qui m'a mis -1, ça aurait sympa de m'indiquer en quoi ma proposition n'était pas pertinente, histoire d'apprendre d'une éventuelle erreur :roll:

Oups, désolé mais c'étais juste par erreur.