Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel [VBA] SOMME.SI.ENS avec date inférieure

[VBA] SOMME.SI.ENS avec date inférieure

18 réponses
Avatar
HD
Bonjour,

J'ai un souci avec la fonction "SOMME.SI.ENS". Je souhaite l'utiliser en VBA
(par souci de rapidité d'exécution) j'ai donc la ligne suivant :
Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1, Sheets(NomD).Columns(ColDate), "<=" &
Da2)

Je n'obtiens rien... alors que si je tape la fonction à la main :
=SOMME.SI.ENS(Sheet1!AO2:AO11414;Sheet1!AY2:AY11414;I16;Sheet1!AE2:AE11414;"<=30/6/2008")

Là j'obtiens bien une valeur... Le souci se pose donc vraissemblablement
avec les quote ". Mais même si j'ajoute des chr(34) de chaque côté du
critère je n'ai toujours rien.

Auriez vous une solution ?

Merci d'avance pour votre aide
--
@+
HD
Signaler un problème avec ce contenu

8 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
HD
Public Function MasseCrédit(a1 As Range, a2 As String, nCpt1 As String)
Que représente la valeur A1 ? A2 ? nCpt1 ?
Donne un exemple réel comment tu passes tes valeurs à la fonction
x = MasseCrédit(A1, A2, nCpt1)


A1 représente la date début des données à récupérer... c'est une variable
qui n'est pas encore utilisée vu que j'ai déjà des soucis avec A2...
A2 correspond à la date de fin des données à récupérer.
nCpt1 correspond au numéro du compte.

En fait, je cherche à avoir dans un ensemble de ligne comptable la somme des
lignes du compte nCpt1 entre les dates A1 et A2.

N.B- Ce n'est JAMAIS une bonne idée d'utiliser comme nom d'un paramètre
ou d'une variable, une chaîne de caractère identique (A1, A2) à ce qui
pourrait
être une adresse de cellule d'une feuille de calcul.


Ok, je vais revoir ça...

Par contre, je me demande si le souci ne vient pas que je met en argument du
SUMIFS non pas une plage de cellule [AE2:AE11414] mais une colonne
Columns(31)... car en première ligne, j'ai le nom des en-têtes des
colonnes... et il se peut que la comparaison sur la date bloque...

--
@+
HD

"michdenis" a écrit dans le message de news:
ucPgD%
Ta ligne de déclaration de ta fonction
Public Function MasseCrédit(a1 As Range, a2 As String, nCpt1 As String)

Que représente la valeur A1 ? A2 ? nCpt1 ?
Donne un exemple réel comment tu passes tes valeurs à la fonction
x = MasseCrédit(A1, A2, nCpt1)

N.B- Ce n'est JAMAIS une bonne idée d'utiliser comme nom d'un paramètre
ou d'une variable, une chaîne de caractère identique (A1, A2) à ce qui
pourrait
être une adresse de cellule d'une feuille de calcul.



"HD" a écrit dans le message de groupe de discussion :
hrbmss$19i1$
Alors... Lorsque je lance :

Sub test()
Dim MaDate As Date
MaDate = DateSerial(2008, 6, 30) 'La date que tu veux !

MsgBox
Evaluate("SUMIFS(Sheet1!AO2:AO11414,Sheet1!AY2:AY11414,I16,Sheet1!AE2:AE11414,""<="
& Format(MaDate, "m/d/yy") & """)")
End Sub

J'obtiens bien l'affichage de la valeur attendue... mais lorsque je lance
:

1 Public Function MasseCrédit(a1 As Range, a2 As String, nCpt1 As String)
2 On Error Resume Next
3 Application.EnableEvents = False
4 Dim i As Long
5 Dim Da2 As Date
6 Application.Volatile False
7
8 i = 1
9 If IsDate(a2) Then
10 Da2 = CDate(a2)
11 MasseCrédit = Evaluate("SUMIFS(" & Sheets(NomD).Columns(ColCredit)
&
"," & Sheets(NomD).Columns(ColCpt1) & "," & nCpt1 & "," &
Sheets(NomD).Columns(ColDate) & ",""<=" & Format(Da2, "m/d/yy") & """)")
12 MsgBox "ColCpt1=" & ColCpt1 & vbCrLf & "NomD=" & NomD & vbCrLf &
"ColCredit=" & ColCredit & vbCrLf & "ColDate=" & ColDate & vbCrLf & "Da2="
&
Format(Da2, "m/d/yy")
13 MsgBox Evaluate("SUMIFS(" & Sheets(NomD).Columns(ColCredit) &
","
& Sheets(NomD).Columns(ColCpt1) & "," & nCpt1 & "," &
Sheets(NomD).Columns(ColDate) & ",""<=" & Format(Da2, "m/d/yy") & """)")
14 End If
15 Application.EnableEvents = True
16 End Function

Je n'ai pas la MasseCrédit qui se calcul (ligne 11), toutes mes variables
qui s'affichent et me montrent que tout est bien initialisé (ligne 12), et
l'affichage de mon Evaluate ne se fait même pas (ligne 13)...

--
@+
HD

Avatar
michdenis
| Par contre, je me demande si le souci ne vient pas que je met en argument du
| SUMIFS non pas une plage de cellule [AE2:AE11414] mais une colonne
| Columns(31)... car en première ligne, j'ai le nom des en-têtes des
| colonnes... et il se peut que la comparaison sur la date bloque...

Même si dans la chaîne de caractères de la fonction, on n'insère pas
des accolades... c'est une fonction matricielle. Sauf erreur, aucune des
versions précédentes à Excel 2007 ne peut résoudre une formule
matricielle si on réfère à l'intégralité d'une colonne de A1 à A65536.
Le maximum permis est 65536 -1
À un autre niveau, si on utilise de grandes plages de cellules inutilement dans
une formule matricielle, on augmente sensiblement le temps de
traitement de ces fonctions. Il faut être imaginatif et essayer de
cerner seulement la plage qui nous intéresse par un nom (plage nommée.)
Avatar
HD
A ) si le paramètre A2 est une date dans une cellule, pourquoi utiliser
A2 As String dans la déclaration de ta fonction au lieu de As Range ?


Car je préfére tester que la variable soit bien une date. D'où la ligne <<
If IsDate(a2) Then Da2 = CDate(a2) >> comme celà si a2 n'est pas une date
alors je ne la prend pas en compte. Car pour cette fonction l'argument sera
paramétré ou non.
Pour ce qui est de la variable A1 qui sera également une date et que je
déclarerais en String, c'est vrai que pour l'instant je l'ai laissé en
Range... mais elle est pour l'instant inutilisée.

B ) Dans ta fonction, plusieurs variables sont non renseignées comme
ColCredit, ColCpt1, ColDate ... Quelles sont leurs valeurs ? Comment
les renseignes-tu ?


Ce sont des valeurs qui ont pour portée le module (et que je déclare donc en
tout début de module or fonction et or sub) et auquels j'affecte une valeur
suivant une macro lancée à l'ouverture du classeur ou à la demande. J'ai
contrôlé ces valeurs et elles sont bien affectées.

C ) Quel rôle ces lignes de code : "Application.EnableEvents = True" doit
jouer dans la fonction ? Une fonction a pour but de retourner un
résultat d'opération. La valeur retournée peut être une chaîne de
caractère, un nombre, une valeur booléenne ou une valeur d'erreur.


Cette fonction est utilisée dans un grand nombre de cellule j'ai donc placé
un EnableEvents en pensant que celà accélérerait le calcul...

D ) La gestion d'erreur de ce genre : On Error Resume Next n'est pas
vraiment
appropriée pour une fonction personnalisée. De toute façon, si la
fonction
ne réussit pas à évaluer la proposition, elle va retourner elle-même
une valeur
d'erreur. Par contre, cette ligne de code peut masquer une erreur de
programmation qui n'a rien à voir avec l'objectif de la fonction et là,
tu auras
de la difficulté à trouver pourquoi ta fonction ne renvoie pas la
valeur désirée.
Lors de tes tests, utilise des points d'arrêt dès le début de la
fonction... et
utilise
le pas à pas pour déterminer la ligne de code problème.


J'ai mis en commentaire la gestion d'erreur... mais je n'ai toujours aucun
message d'erreur... la fonction me retourne juste un "#VALEUR" depuis que
j'utilise l'Evaluate(SUMIFS

MsgBox Evaluate("SUMIFS(" & Sheets(NomD).Columns(ColCredit) & _
"," & Sheets(NomD).Columns(ColCpt1) & "," & nCpt1 & _
"," & Sheets(NomD).Columns(ColDate) & ",""<=" & _
Format(Da2, "m/d/yy") & """)")


Le souci est que ce code ne me ressort pas de somme... Et même lorsque je
simplifie la fonction en enlevant la dernière condition celà ne fonctionne
pas non plus.

MsgBox Evaluate("SUMIFS(" & Sheets(NomD).Columns(ColCredit) & _
"," & Sheets(NomD).Columns(ColCpt1) & "," & nCpt1)
Ne m'affiche rien... Le SUMIFS doit planter... alors que si j'utilise:
MasseCrédit =
Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1)
Là j'obtiens bien un résultat...

C'est comme si un argument du SUMIFS n'était pas correct... Peut on donner
dans un Evaluate SUMIFS en argument une colonne située sur une autre feuille
?

--
@+
HD
Avatar
HD
MsgBox Evaluate("SUMIFS(" & Sheets(NomD).Columns(ColCredit) & _
"," & Sheets(NomD).Columns(ColCpt1) & "," & nCpt1)
Ne m'affiche rien... Le SUMIFS doit planter... alors que si j'utilise:
MasseCrédit =
Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1)
Là j'obtiens bien un résultat...

C'est comme si un argument du SUMIFS n'était pas correct... Peut on donner
dans un Evaluate SUMIFS en argument une colonne située sur une autre
feuille?




MsgBox Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1, Sheets(NomD).Columns(ColDate), Da2)
Me ressort bien une donnée...

Je viens d'essayer avec:
Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1 + Len(nCpt1) - 1), nCpt1,
Sheets(NomD).Columns(ColDate), "<=" & Format(Da2, "mm/dd/yy"))

Et là ça fonctionne !!!

Je ne sais pas ce qui ne passe pas avec l'EVALUATE SUMIFS... ???
--
@+
HD
Avatar
HD
Voilà ma nouvelle fonction...

Public Function MasseCrédit(a1 As String, a2 As String, nCpt1 As String)
Dim i As Long
Dim Da2 As Date

Application.Volatile False
If IsDate(a2) Then
Da2 = CDate(a2)
MasseCrédit =
Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1, Sheets(NomD).Columns(ColDate), "<=" &
Format(Da2, "mm/dd/yy"))
Else
MasseCrédit =
Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1)
End If
End Function


--
@+
HD
Avatar
michdenis
Voici un exemple maison pour illustrer la saisie avec "Evaluate"

Évidemment, tu dois adapter le nom de la feuille "Sheet1" pour
celle de ton application ainsi que les références des plages.

à partir de là, il y a des variantes.... il s'agit d'avoir un peu de patience !
'-------------------------------------
Public Function MasseCrédit()

Dim Da2 As Date, Adr As String, nCpt1 As String
Dim Adr1 As String, Adr2 As String, X As String
Dim Y As Long

Adr = "Sheet1!" & Range("A1:A10").Address
Adr1 = "Sheet1!" & Range("B1:B10").Address
Adr2 = "Sheet1!" & Range("C1:C10").Address

nCpt1 = "Sheet1!" & Range("D1").Address
Da2 = Date

Y = Evaluate("SUMIFS(" & Adr & _
"," & Adr1 & "," & nCpt1 & _
"," & Adr2 & ",""<=" & _
Format(Date, "m/d/yy") & """)")

End Function
'-------------------------------------



"HD" a écrit dans le message de groupe de discussion :
hrc490$1epg$
Voilà ma nouvelle fonction...

Public Function MasseCrédit(a1 As String, a2 As String, nCpt1 As String)
Dim i As Long
Dim Da2 As Date

Application.Volatile False
If IsDate(a2) Then
Da2 = CDate(a2)
MasseCrédit Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1, Sheets(NomD).Columns(ColDate), "<=" &
Format(Da2, "mm/dd/yy"))
Else
MasseCrédit Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1)
End If
End Function


--
@+
HD
Avatar
HD
Je vais voir pour adapter ça... car effectivement, passer directement par
des variables Adr simplifie la lisibilité de l'Evaluate...

Un grand MERCI à vous deux !!!

--
@+
HD

"michdenis" a écrit dans le message de news:

Voici un exemple maison pour illustrer la saisie avec "Evaluate"

Évidemment, tu dois adapter le nom de la feuille "Sheet1" pour
celle de ton application ainsi que les références des plages.

à partir de là, il y a des variantes.... il s'agit d'avoir un peu de
patience !
'-------------------------------------
Public Function MasseCrédit()

Dim Da2 As Date, Adr As String, nCpt1 As String
Dim Adr1 As String, Adr2 As String, X As String
Dim Y As Long

Adr = "Sheet1!" & Range("A1:A10").Address
Adr1 = "Sheet1!" & Range("B1:B10").Address
Adr2 = "Sheet1!" & Range("C1:C10").Address

nCpt1 = "Sheet1!" & Range("D1").Address
Da2 = Date

Y = Evaluate("SUMIFS(" & Adr & _
"," & Adr1 & "," & nCpt1 & _
"," & Adr2 & ",""<=" & _
Format(Date, "m/d/yy") & """)")

End Function
'-------------------------------------



"HD" a écrit dans le message de groupe de discussion :
hrc490$1epg$
Voilà ma nouvelle fonction...

Public Function MasseCrédit(a1 As String, a2 As String, nCpt1 As String)
Dim i As Long
Dim Da2 As Date

Application.Volatile False
If IsDate(a2) Then
Da2 = CDate(a2)
MasseCrédit > Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1, Sheets(NomD).Columns(ColDate), "<="
&
Format(Da2, "mm/dd/yy"))
Else
MasseCrédit > Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1)
End If
End Function


--
@+
HD

Avatar
michdenis
| Public Function MasseCrédit(a1 As String, a2 As String, nCpt1 As String)

Lorsque tu définis un paramètre de ta fonction comme ( Rg As Range )
lorsque ce dernier est inscrit dans une cellule, cela te permet de retrouver
la feuille où tu extraits ce "Range" par :
Adr = Rg.Parent.Name & "!" & Rg.address
au lieu de coder en dur le nom de la feuille dans ta fonction. Cette dernière
devient beaucoup plus flexible. Si tu désires tester le contenu de la cellule,
rien ne t'empêche de le faire ! Dans le cas, d'un critère "Date", la saisie de
la fonction dans la cellule est plus simple et rapide.
Le paramètre passé As String n'offre pas les mêmes possibilités. Si en plus
tu dois utiliser cette fonction sur différents ordinateurs qui ont des paramètres
variés dans les options régionales du panneau de configuration, si tu inscris
dans ta fonction = MaFonction("08/04/10"), l'interprétation de la date que
fera le code de ta fonction personnalisée risque de s'empêtrer. Les fonctions
de conversion telle que Cdate() utilise les paramètres du panneau de
configuration pour transformer la chaîne de caractères de la date.

Voilà.



"HD" a écrit dans le message de groupe de discussion :
hre4pr$27m2$
Je vais voir pour adapter ça... car effectivement, passer directement par
des variables Adr simplifie la lisibilité de l'Evaluate...

Un grand MERCI à vous deux !!!

--
@+
HD

"michdenis" a écrit dans le message de news:

Voici un exemple maison pour illustrer la saisie avec "Evaluate"

Évidemment, tu dois adapter le nom de la feuille "Sheet1" pour
celle de ton application ainsi que les références des plages.

à partir de là, il y a des variantes.... il s'agit d'avoir un peu de
patience !
'-------------------------------------
Public Function MasseCrédit()

Dim Da2 As Date, Adr As String, nCpt1 As String
Dim Adr1 As String, Adr2 As String, X As String
Dim Y As Long

Adr = "Sheet1!" & Range("A1:A10").Address
Adr1 = "Sheet1!" & Range("B1:B10").Address
Adr2 = "Sheet1!" & Range("C1:C10").Address

nCpt1 = "Sheet1!" & Range("D1").Address
Da2 = Date

Y = Evaluate("SUMIFS(" & Adr & _
"," & Adr1 & "," & nCpt1 & _
"," & Adr2 & ",""<=" & _
Format(Date, "m/d/yy") & """)")

End Function
'-------------------------------------



"HD" a écrit dans le message de groupe de discussion :
hrc490$1epg$
Voilà ma nouvelle fonction...

Public Function MasseCrédit(a1 As String, a2 As String, nCpt1 As String)
Dim i As Long
Dim Da2 As Date

Application.Volatile False
If IsDate(a2) Then
Da2 = CDate(a2)
MasseCrédit > Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1, Sheets(NomD).Columns(ColDate), "<="
&
Format(Da2, "mm/dd/yy"))
Else
MasseCrédit > Application.WorksheetFunction.SumIfs(Sheets(NomD).Columns(ColCredit),
Sheets(NomD).Columns(ColCpt1), nCpt1)
End If
End Function


--
@+
HD

1 2