SUMIFS--formule - erreur 13-excel VBA

Version imprimable

Bonjour,

Je suis pas très doué en VBA mais j'ai besoin d'aide...

Je voudrais sur une plage donné, affichier des résultats tirés d'une autre plage d'un onglet différent à partir de la formule "SUMIFS":

J'ai des méssages d'erreur de type "13". Quelqu'un peut relire mon code et m'apporter une aide....
voici mon code:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Sub ligneremplies() Dim f As Integer Dim qname, qdb, qdf, qqty As Range Dim qnom, qdate As Range Dim T As Double Dim AB As Long f = Sheets("TESTCODE").Range("F65536").End(xlUp).Row Set qname = Sheets("TESTCODE").Range("E1:E" & f) Set qdb = Sheets("TESTCODE").Range("F1:F" & f) Set qdf = Sheets("TESTCODE").Range("G1:G" & f) Set qqty = Sheets("TESTCODE").Range("H1:H" & f) Sheets("Planning rotation").Range("XM11:BID359").Select '= T Set qnom = Sheets("Planning rotation").Range("F11:F359") Set qdate = Sheets("Planning rotation").Range("XM7:BID7") Range("XM11:BID359").Value = WorksheetFunction.SumIfs(qqty, qname = qnom, qdb <= qdate, qdf >= qdate) End Sub

29/06/2013, 09h36
alsimbad

salut
A priori, mais sans certitude a cette heure là, tes déclarations dont les noms commencent par Q sont des ranges
T et AB sont des nombres mais n'apparaissent pas dans les critères
dans le code tous tes "set" déterminent des ranges et noms des nombres
je ne connais pas sumif, j'ai regardé a somme.si et il te faut un critère, et le nombre d'argument ne correspond pas.
si ça peut t'aider ..
29/06/2013, 10h00
casefayere

Bonjour le forum, expertdev, alsimbad,

Citation:

je ne connais pas sumif, j'ai regardé a somme.si et il te faut un critère, et le nombre d'argument ne correspond pas.
si ça peut t'aider ..

Non, Sumifs (avec un "s") remplace "SOMME.SI.ENS", où plusieurs critères peuvent être déclarés, je crois que cette fonction existe depuis Excel 2007.

Mais il est vrai que les variables sont mal déclarées, mal référencées et que la formule comporte des erreurs car les critères ne peuvent pas être des plages comme dans "SOMME.si".

Un petit fichier m'aiderais à orienter expertdev

Bonne journée à tou(te)s
29/06/2013, 17h27
expertdev

1 pièce(s) jointe(s)

fichier test exemple SUMIFS

Bonjour casefayere,

Merci pour vos réponses, voici mon fichier à titre d'exemple.
Un exemple de mon fichier car il est lourd et comporte plusieurs lignes et colonnes. Je voudrais remplacers somme.si.ens par la même formule en code VBA.

Merci
29/06/2013, 17h48
casefayere

Bonjour Experdev,

Je vais regarder mais en attendant corriges ton 1er message en respectant les balises codes, en pratiquant comme ci-dessous, ou pendant une redaction comme ici :

En regardant ton fichier et, sutout, ta formule, je ne comprends pas quelle plage veux-tu totaliser.
Code:

1 2 3 'quelle plage car la formule est incorrecte ? .Range("XM11:BID359") = WorksheetFunction.SumIfs(qqty, qname = qnom, qdb <= qdate, qdf >= qdate) 'et quels critères ?

29/06/2013, 18h26
expertdev

Le fichier envoyé est un exemple ou un fichier text car dans le fichier réel la plage est bien celle qui est citée:

Mon but est de transformer cette formule en code VBA mais en respectant la plage d'application:

Ma formule sous cellules -excel:
"=SOMME.SI.ENS(TESTCODE!$H:$H;TESTCODE!$E:$E;$F11;TESTCODE!$F:$F;"<="&VU$7;TESTCODE!$G:$G;">="&VU$7)"

---exemple de la cellule "VU11"

Plage d'application:

Code:

Sheets("Planning rotation").Range("XM11:BID359")

Je pense qu'avec sumifs, tes totaux sont impossibles, pensant avoir compris ce que tu cherches, il faut un peu de temps pour arriver à un résultat mais je dois partir (eh oui, on est samedi...), si personne n'a pu t'aider entre temps, je regarderai demain.

Bonne soirée

Bonjour,
après réflexion et, bien sur, si j'ai compris, le code reviendrait à ça, en tenant compte de ce que tu as donné comme indication :

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
Sub ligneremplies()
 
Dim derlg As Long
Dim qname As Range, qdb As Range, qdf As Range, qqty As Range
Dim qnom As Range, qdate As Range
Dim x As Long, y As Long
With Sheets("TESTCODE")
  derlg = .Range("F" & .Rows.Count).End(xlUp).Row
  Set qname = .Range("E1:E" & derlg)
  Set qdb = .Range("F1:F" & derlg)
  Set qdf = .Range("G1:G" & derlg)
  Set qqty = .Range("H1:H" & derlg)
End With
With Sheets("Planning rotation")
  Set qnom = .Range("F11:F359")
  Set qdate = .Range("XM7:BID7")
  For x = 637 To 961
    For y = 11 To 359
      .Cells(y, x) = WorksheetFunction.SumIfs(qqty, qname, .Cells(y, 6), qdb, "<= .Cells(7, x)", qdf, ">= .Cells(7, x)")
    Next y
  Next x
End With
 
End Sub

Mais attention, ce code est très long à l'exécution, il faudrait passer par des variables tableau. J'attends déjà ta réponse.

PS : je viens de calculer, avec le fichier fourni > 54 secondes

Avec ce 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
Sub ligneremplies()
 
Dim derlg As Long
Dim qname As Range, qdb As Range, qdf As Range, qqty As Range
Dim qnom As Range, qdate As Range, tbl(1 To 359, 1 To 954)
Dim x As Long, y As Long, i As Long, j As Long, start As Single
Application.ScreenUpdating = False
start = Timer
With Sheets("TESTCODE")
  derlg = .Range("F" & .Rows.Count).End(xlUp).Row
  Set qdb = .Range("F1:F" & derlg)
  Set qdf = .Range("G1:G" & derlg)
  Set qname = .Range("E1:E" & derlg)
  Set qqty = .Range("H1:H" & derlg)
End With
With Sheets("Planning rotation")
  Set qnom = .Range("F11:F359")
  Set qdate = .Range("XM7:BID7")
  i = 0: j = 0
  For x = 637 To 961
    i = i + 1
    For y = 11 To 359
      j = j + 1
      tbl(j, i) = WorksheetFunction.SumIfs(qqty, qname, .Cells(y, 6), qdb, "<= cdate(.Cells(7, x))", qdf, ">= cdate(.Cells(7, x))")
      '.Cells(y, x) = WorksheetFunction.SumIfs(qqty, qname, .Cells(y, 6), qdb, "<= cdate(.Cells(7, x))", qdf, ">= cdate(.Cells(7, x))")
    Next y
    j = 0
  Next x
  .Range("XM11:BID359") = tbl
End With
Application.ScreenUpdating = True
MsgBox "durée du traitement: " & Timer - start & " secondes"
 
End Sub

+- 15 secondes, des améliorations seront donc à faire

Bonne journée

01/07/2013, 00h58
expertdev

aucun résultat SUMIFS

Merci pour le travail, j'ai eaasyé les codes mais j'ai un petit problème au final mais cellules sont sans valeurs, elles ne prennent pas en compte les résultats de la feuille ("TESTCODE") en colonne "H".
Le temps d'xécution dans mon fichier est de 271,234 secondes.
Le problème ne vient il pas des dates "cdate" et "critères qdb & qdf"
Merci
01/07/2013, 08h08
casefayere

Bonjour le forum, expertdev,

Citation:

Le temps d'xécution dans mon fichier est de 271,234 secondes.

Même en passant par un tableau ? (2ème code envoyé)

Citation:

cellules sont sans valeurs, elles ne prennent pas en compte les résultats de la feuille ("TESTCODE") en colonne "H".

Vérifies tes dates et leur format, vérifies également tes conditions ">=" et "<="

En effet, sur le fichier que tu as envoyé tu as beaucoup de dates qui n'existent pas, style "00/01/1900", même si c'est un fichier bidon, il vaut mieux mettre des dates qui correspondent à des dates.

Bon courage et bonne journée
01/07/2013, 16h58
expertdev

SUMIFS entre deux date...date de début & date de fin

Bonjour,

J'ai essayé de modifier les dates et certains critaires "<=" et ">=" mais il semble qu'il ait un résultat qui est "0" dans les cellules.

Le but de mon fichier est de:
--> remplir les cellules d'une plage de la feuille("Planning rotation") en fonction:
a) d'un code sur une plage en colonne
b) d'une date ("qdate") sur une plage en ligne qui doit répondre à la condition "<=" et ">="
c) pour chaque code "qname=qnom"; dans la cellule d'intersection, la date correspondante "qdate" doit répondreà la condition "<=" et ">=" à la date de début "qdb" et date de fin "qdf" de la feuille ("TESTCODE") en fonction de de "qname" code de cette feuille.

Avec la formule:
=SOMME.SI.ENS(TESTCODE!$H:$H;TESTCODE!$E:$E;$F11;TESTCODE!$F:$F;"<="&VU$7;TESTCODE!$G:$G;">="&VU$7)"
en exemple posé sur une cellule le résultat est vérifié et même si les date sont en format 01/01/1900 mais le processus de calcule est long environ 10mn.

Merci pour la solution .
01/07/2013, 17h15
casefayere

Bonjour,
Que te dire de plus que ce que j'ai spécifié dans mon message précédent, as-tu essayé avec le second code qui utilise une variable tableau ? as-tu vérifié tes données, qui doivent être écrites à l'identique sur les 2 tableaux, attention aux espaces qui risquent de fausser le résultat.......

Tu n'as envoyé qu'un exemple mais beaucoup de cellules sont vides, as-tu reformaté tes cellules de date même si tu as mis des dates correctes à la place des "00/01/1900"

enfin, si ton fichier est beaucoup plus long, je te rappelle que sur mon PC, j'arrive à +- 12 secondes, alors avec le fichier original, le temps va s'allonger.

Sinon, je pense encore que la formule que j'ai proposée est correcte, mais je ne suis qu'un pauvre humain et pas à l'abri des erreurs

Bon courage

bonjour,
tu as une erreur 13 (incompatibilité de type):
tu es typiquement confronté à un problème culturel entre VBA et Windows option régionale!
je m'explique VBA ne comprend que le [.] comme séparateur décimal et Windows option régional [,]. quand tu concatène du texte avec un double,
tu te retrouve avec:
Code:

1 2 3 4 5 6 qqty=1,5 WorksheetFunction.SumIfs(qqty, qname = qnom, qdb <= qdate, qdf >= qdate) WorksheetFunction.SumIfs("1,5", qname = qnom, qdb <= qdate, qdf >= qdate) 'et vba attend WorksheetFunction.SumIfs("1.5", qname = qnom, qdb <= qdate, qdf >= qdate) WorksheetFunction.SumIfs(replace("1,5",",","."), qname = qnom, qdb <= qdate, qdf >= qdate)

01/07/2013, 20h29
expertdev

VBA formule avec enrégistrement SUMIFS
Rebonjour!

avec un unrégistrement automatique j'ai cette formule:
Code:

1 2 ActiveCell.FormulaR1C1 = _ "=SUMIFS(TESTCODE!C8,TESTCODE!C5,RC6,TESTCODE!C6,""<=""&R7C,TESTCODE!C7,"">=""&R7C)"
Comment la transformer en valeur sans afficher la formule et avoir une exécution rapide....
Merci
02/07/2013, 03h48
nibledispo

sumifs--formule erreur 13 excel vba

bonjour,

je découvre cette formule et suis donc allé dans l'aide Excel.

Il est écrit :
"Contrairement aux arguments de critère et de plage de la fonction SOMME.SI, dans la fonction SOMME.SI.ENS, chaque argument plage_critères doit contenir le même nombre de lignes et de colonnes que l’argument somme_plage."

Est-on certain que la plage des critères est identique à celle de la somme. Les premiers et le second se trouvant sur des feuilles différentes, rien n'est moins sur.
En effet, si la plage de la somme est connu celle des critères ne l'est pas à priori à en juger par la recherche de la dernière ligne.

Cordialement.

SUMIFS

Bonjour,
en utilisant l'enrégistrement automatique de VBA je tombe à des résultats correctes avec lecode ci-dessous sauf qu'il est long et je suis obligé d'éviter ou contouner le recalcule de la feuille par des méthodes de copier coller:

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
Sub Macro2()
'
' Macro2 Macro
'
' Touche de raccourci du clavier: Ctrl+Shift+T
Application.ScreenUpdating = False
start = Timer
 
    Sheets("Planning rotation").Select
    Range("XM11:BID359").Select
    Selection.FormulaR1C1 = _
        "=SUMIFS(TESTCODE!C8,TESTCODE!C5,RC6,TESTCODE!C6,""<=""&R7C,TESTCODE!C7,"">=""&R7C)"
 
    Range("XM11:BID359").Select
    Application.CutCopyMode = False
    Selection.Copy
 
    Sheets("Feuil1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Range("A1:AJR349").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Planning rotation").Select
    Range("XM11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.ScreenUpdating = True
MsgBox "durée du traitement: " & Timer - start & " secondes"
 
End Sub

Quelqu'un peut-il maider à réecrire cette fonction pour un temps moins long et une performance éfficace (temps actuel 704 secondes...).

Merci

Bonjour le forum, expertdev

D'après ton dernier code, tu injectes les formules en feuille "Planning rotation" pour ne copier que la valeur dans une autre feuille "Feuil1", tu reprends la copie de "Feuil1" pour réinjecter la valeur dans "Plannig rotation". San avoir testé, ne pourrais-tu pas simplement faire :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub Macro2() Application.ScreenUpdating = False Dim start As Single start = Timer With Sheets("Planning rotation") .Range("XM11:BID359").FormulaR1C1 = _ "=SUMIFS(TESTCODE!C8,TESTCODE!C5,RC6,TESTCODE!C6,""<=""&R7C,TESTCODE!C7,"">=""&R7C)" .Range("XM11:BID359") = .Range("XM11:BID359").Value 'ici tu ne reprends que les valeurs End With Application.ScreenUpdating = True MsgBox "durée du traitement: " & Timer - start & " secondes" End Sub
et sans passer par les "Select", tu devrais gagner du temps

Bonjour casefayere,

Merci pour ta formule, elle est super simple mais le temps est toujours le même 704 secondes. Je crois que c'est le calcul de la formule qui alourdi le processus.

N'ya t-il pas une autre formulation de cette formule?

Code:

1
2
3
4
5
With Sheets("Planning rotation")
  .Range("XM11:BID359").FormulaR1C1 = _
        "=SUMIFS(TESTCODE!C8,TESTCODE!C5,RC6,TESTCODE!C6,""<=""&R7C,TESTCODE!C7,"">=""&R7C)" 'réecrire cette formule sans qu'elle fasse le calcule qui prend trop de temps 
  .Range("XM11:BID359") = .Range("XM11:BID359").Value 'ici tu ne reprends que les valeurs
End With

Pour l'utilisation simple avec ton code...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Macro2()
Application.ScreenUpdating = False
Dim start As Single
start = Timer
With Sheets("Planning rotation")
  .Range("XM11:BID359").FormulaR1C1 = _
        "=SUMIFS(TESTCODE!C8,TESTCODE!C5,RC6,TESTCODE!C6,""<=""&R7C,TESTCODE!C7,"">=""&R7C)"
  .Range("XM11:BID359") = .Range("XM11:BID359").Value 'ici tu ne reprends que les valeurs
End With
Application.ScreenUpdating = True
MsgBox "durée du traitement: " & Timer - start & " secondes"
 
End Sub

Merci d'avance...

SUMIFS ou Sumproduct ou RechrcheV entre deux date...date de début & date de fin

Bonjour,
"A l'AIDEEEEEEE"

Pas toujours de solutions à mon problème. J'ai essayé toutes les méthodes et simplifié ma formule. Je voudrais faire le processus suivant:

Ma fauille ("TESTCODE")--> il y'a 4 colonnes
-Colonne "A"= code alpha numérique=sousVBA(qname)
-Colonne "B": des dates, qui correspondent à une date de début=sousVBA(qdb)
-Colonne "C": des dates, qui correspondent à une date de fin=sousVBA(qdf)
-Colonne "D": valeurs numériques=sousVBA(qqty)
Sous cette feuille un code peut avoir plusieurs "qdb" et "qdf" les unes à la suite des autres sans se chevaucher.

===> Mon but final, c'est de ranger ces informations dans une autre feuille qui constitue mon planning et son nom est ("planning rotation"). Ces informations seront rangées dans une plage en fonction de deux critères:
-Colonne "B"--> Range( "B11:B359"): renferment les codes de la colonne "A" de la feuille("TESTCODE"), certaines lignes sont vides. et les codes rangés au pif selon mon desir; =sousVBA(qnom).
-Ligne(7)--> Range("XM7:BID7"): renfermant des dates =sousVBA(qdate).

La plage de rangement des données est ("XM11:BID359"), les valeurs se rangent en fonction de ces critères
--->"qnom"="qname"
--->"qdate">="qdb"
--->"qdate"<="qdf"
---Alors donne la valeur qui correspond aux critères de la colonne "qqty" de la feuille ("TESTCODE").

Avec la formule "Sumifs" je trouve mon compte mais le temps de reponse est de 25mn.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub Macro2() Application.ScreenUpdating = False Dim start As Single start = Timer With Sheets("Planning rotation") .Range("XM11:BID359").FormulaR1C1 = _ "=SUMIFS(TESTCODE!C4,TESTCODE!C1,RC2,TESTCODE!C2,""<=""&R7C,TESTCODE!C3,"">=""&R7C)" .Range("XM11:BID359") = .Range("XM11:BID359").Value 'ici tu ne reprends que les valeurs End With Application.ScreenUpdating = True MsgBox "durée du traitement: " & Timer - start & " secondes" End Sub
A l'aideeeee svp!

Planning VBA - excel

Bonjour,

J'aimerais avoir une explication pas à pas du code ci-dessous car je crois que c'est la réponse à ma recherche.

Merci

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
Private Sub Worksheet_Activate()
  Application.ScreenUpdating = False
  debPlan = DateSerial([année], 1, 1)
  Set fbd = Sheets("bd")
  Set fplan = Sheets("planSem1VBA")
  [B4:FZ23].ClearContents
  [B4:FZ23].Interior.ColorIndex = xlNone
  nblignes = fbd.[A1].CurrentRegion.Rows.Count
  For i = 2 To nblignes
    nom = fbd.Cells(i, 1)
    Set result = fplan.[A:A].Find(What:=nom, LookIn:=xlValues)
    If Not result Is Nothing Then
      If fbd.Cells(i, 3) < DateSerial([année], 7, 1) Then
        début = fbd.Cells(i, 2) - debPlan + 2
        fin = fbd.Cells(i, 3) - debPlan + 2
        Stage = fbd.Cells(i, 4)
        fplan.Cells(result.Row, début) = Stage
        lig = result.Row
        For d = début To fin
          fplan.Cells(result.Row, d).Interior.ColorIndex = 6
        Next d
      End If
    End If
  Next i
End Sub