IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Transposer somme.si.ens avec critère sur date et heures en vba ? [XL-2010]


Sujet :

Excel

  1. #1
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut Transposer somme.si.ens avec critère sur date et heures en vba ?
    bonjour,

    j'aurais besoin de votre aide pour transposer cette formule excel en code vba si cela est possible ?

    avec peut-être
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Application.WorksheetFunction.SumIfs
    ? mais pour la suite ?!... ou bien avec ?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME.SI.ENS($AF$5:AF5;$B$5:B5;">="&TEMPS(HEURE(B5);MINUTE(B5);SECONDE(B5))-TEMPS(0;0;10)+DATE(ANNEE(B5);MOIS(B5);JOUR(B5)))
    merci par avance

  2. #2
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Il faut éviter d'essayer de reproduire en code VBA ce qu'on fait avec des fonctions Excel. Ce n'est pas du tout la même philosophie.

    Ici, en l’occurrence, le plus simple est de te faire une boucle For...to avec un If.
    Tu gagneras en temps de développement sans perdre en temps d'exécution.
    Il est inutile de se compliquer la vie en essayant de faire ça en une seule ligne. Les contraintes du VBA ne sont pas celles des formules dans la feuille.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  3. #3
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Salut.

    Tu vas avoir deux avis pour le prix d'un... Sauf qu'ils seront contradictoires .

    Perso, je privilégie de loin l'utilisation de fonctions telles que sumifs à des boucles (voir ma signature à ce propos ^^). Pourquoi? Parce que les boucles, mine de rien, c'est de la vraie programmation, et cela demande donc vite un certain bagage en langage de programmation, en gestion des variables et autres "babioles".

    Alors que la transposition d'une formule Excel en VBA est quelque chose de "relativement" simple, et que tu peux en fait travailler sur plusieurs axes en fonction de ce que tu veux réaliser, puisque tu peux:
    écrire la formule dans une cellule (puis éventuellement faire un copier/coller spécial valeurs en VBA;
    utiliser la plupart des formules via Application.WorksheetFunctions;
    utiliser Evaluate("...");

    Chacune des possibilités répondant à un besoin précis. Pourrais-tu préciser ton besoin?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  4. #4
    Expert éminent

    Homme Profil pro
    Curieux
    Inscrit en
    Juillet 2012
    Messages
    5 071
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Curieux
    Secteur : Arts - Culture

    Informations forums :
    Inscription : Juillet 2012
    Messages : 5 071
    Points : 9 850
    Points
    9 850
    Billets dans le blog
    5
    Par défaut
    Bonjour,

    je me pose en conciliateur

    je suis entièrement d'accord sur le fait qu'il est préférable de passer par les formules Excel dans ta macro, plutôt qu'une boucle
    la formule sera moins lourde.. elle est native dans Excel
    la boucle va consommer des ressources, prendre du temps à s'effectuer etc...


    MAIS !!

    quand je regarde ta formule , je me dis qu'il faudra peut être :

    - écrire la formule en VBA (Application.Worksheetfunction.sumifs etc..)
    - réaliser une boucle pour remplir tes cellules avec cette boucle, dans la mesure où ta plage d'addition va évoluer au fur et à mesure des lignes

    (on est d'accord que c'est pour automatiser ton fichier qui fait la somme sur les 10 dernières secondes ?)


    Tu vois, rien n'est tranché en VBA, on pèse toujours le pour et le contre
    c'est pour ça que tu auras parfois plusieurs avis différents, mais qui donneront toujours un résultat (que la macro soit optimisée ou non)

    le principal : c'est qu'on est tout les 3 (et d'autres contributeurs !) là pour t'aider
    charge à toi de nous apporter de plus amples explications sur la formule que tu veux faire :

    - sur quelle plage ?
    - le comportement attendu ?
    - etc..

  5. #5
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Maitriser les subtilités des boucles While (et autres du même genre) demande en effet une certaine pratique.

    En revanche, comprendre le principe d'une boucle For...to est à la portée du premier venu en 10 min (je parle d'expérience et j'avais 14 ans quand j'ai passé ces 10 min).

    Mais il est vrai que mon avis n'est pas objectif. J'avoue que je préfère utiliser des fonctions simples, basiques, les briques Légo de la programmation, plutôt que des "macro-fonctions" qui font plus de choses mais qu'il est plus difficile de comprendre parfaitement et de maitriser les effets. Il m'arrive même parfois (rarement) de préférer une boucle à un Find (mais je me soigne ).

    C'est une question d'habitude et de sensibilité.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  6. #6
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    On pourrait lancer un débat, ça animerait un peu le forum...

    Du moment que cela ne parte pas en troll, de bonnes choses pourraient en sortir avec exemples, vécu des uns et des autres, etc...

    Tant que cela se fait dans l'esprit des forums, pourquoi pas?

    Mais cela nous éloigne quelque peu de la question initiale...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Citation Envoyé par Menhir Voir le message
    [...]
    En revanche, comprendre le principe d'une boucle For...to est à la portée du premier venu en 10 min (je parle d'expérience et j'avais 14 ans quand j'ai passé ces 10 min).
    [...]
    Oui, mais savoir déterminer le début, et surtout la fin de la boucle, ça peut déjà être plus comique, si on reste en programmation pure => il y a matière à débat...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  8. #8
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    merci pour vos contributions...et voici en gros le but de la formule

    le fichier est une extraction de mesures sur 15 jours faites à peu près à la même heure tout au long de la journée par tranche de 1 secondes à 4 minutes donc il peut y avoir énormément de données ( 230 000 lignes pour 15 jours)
    colonne B : heure (le format d'extraction est du type (25/03/2015 08:10:05) )
    B4 : ligne de la 1ère heures (heures minutes secondes) pris en compte
    AF5 : 1ère données binaire
    colonne AF : données binaire 0 ou 1

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME.SI.ENS($AF$4:AF5;$B$4:B5;">="&TEMPS(HEURE(B5);MINUTE(B5);SECONDE(B5))-TEMPS(0;0;10)+DATE(ANNEE(B5);MOIS(B5);JOUR(B5)))
    colonne AG où je met la formule : calculer sur 10 secondes glissantes précédent l'heure correspondant à la ligne sur laquelle on se trouve la somme des données de la colonne AF


    la formule est très bien mais sur les données d'une journée. Sur plus de données il n'y a plus assez de ressources sur le PC pour faire tous les calculs et tout plante.

    j'espérais que la transposition de cette formule en code vba utiliserait moins de ressources ou par le biais de pause peut-être tous les x calculs pourrait faire en sorte que tout se passe bien jusqu'au bout ?!


    le fichier test : test_vba.xlsm

    merci

  9. #9
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    le fichier sur lequel j'ai intégré la formule test_vba.xlsm que j'aimerais passé sous vba si cela peu aider à mon problème de ressources et de plantage

  10. #10
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Citation Envoyé par novice06 Voir le message
    la formule est très bien mais sur les données d'une journée. Sur plus de données il n'y a plus assez de ressources sur le PC pour faire tous les calculs et tout plante.
    La fonction SOMME.SI.ENS est vraiment très puissante mais elle est aussi TRES gourmande en ressource, comme les formules matricielle.

    L'avantage de passer par une macro en VBA, c'est qu'on peut enchaîner les calculs les uns à la suite des autres au lieu qu'ils soient faits en parallèle comme dans une feuille de calcul. Ca évite de surcharger la mémoire. En somme, au lieu de faire quelques milliers de calculs en même temps, tu les fais les uns après les autres.

    Cela dit, maintenant, la question ne se pose plus : si tu as plusieurs calculs, tu vas devoir un peu toucher aux boucles VBA.
    Au moins un For...to.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  11. #11
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    voici mon code qui fonctionne mais une fois le process terminé je m'aperçois d'une chose en regardant la formule qui s'est inscrit dans la cellule : les plages sont figés avec des $

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME.SI.ENS($AF$4:$AF5;$B$4:$B$5;">="&TEMPS(HEURE($B$5);MINUTE($B$5);SECONDE($B$5))-TEMPS(0;0;10)+DATE(ANNEE($B$5);MOIS($B$5);JOUR($B$5)))
    alors que je voudrais la voir apparaitre comme ceci pour qu'une personne qui rajoute des données intermédiaires par exemple sans avoir a relancé le code puisse simplement tirer la formule vers le bas ?!

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME.SI.ENS($AF$4:$AF5;$B$4:$B5;">="&TEMPS(HEURE($B5);MINUTE($B5);SECONDE($B5))-TEMPS(0;0;10)+DATE(ANNEE($B5);MOIS($B5);JOUR($B5)))

    merci par avance

    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
    sub glissant()
     
    Dim ShtName As String, NameFich As String, chemin As String
    Dim a As Long, derligne As Long
     
     
    chemin = Workbooks(ActiveWorkbook.Name).Path
    NameFich = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
     
    ActiveWorkbook.SaveAs Filename:= _
            chemin & "\" & NameFich & ".xlsm", FileFormat _
            :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
     
     
    derligne = Sheets(ShtName).Range("K65536").End(xlDown).Row
    dercolonn = Range("IV1").End(xlToLeft).Column
     
    choixsec = InputBox("Combien de secondes glissante (1 à 59) ? ", "Calcul glissant")
     
    For a = 5 To derligne
     If Cells(a, 2) = "" Then Exit Sub
     
        Cells(a, dercolonn + 1).FormulaR1C1 = _
            "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"
     
    Next a
     
    MsgBox "Calcul effectué avec succès !", vbOKOnly
    ActiveWorkbook.Save
     
    End Sub

  12. #12
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Si ta macro ne sert qu'à mettre les formules dans les cellules, tu ne gagnes rien en terme de ressources.*
    Si tu veux gagner quelque chose, il faut que ce soit la macro qui fasse les calculs et que tu ne mettes dans les cellules que la valeur des résultats.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    derligne = Sheets(ShtName).Range("K65536").End(xlDown).Row
    Ca, ça ne marche pas.
    Si tu te places dans une cellule en fin de colonne (65536), il faut faire ta recherche de la dernière ligne en remontant (xlUp) et non en descendant.
    Donc choisit entre les deux formules suivante (la première étant préférable) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    derligne = Sheets(ShtName).Range("K65536").End(xlUp).Row
    derligne = Sheets(ShtName).Range("K5").End(xlDown).Row
    Ou, encore mieux
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    derligne = Sheets(ShtName).Cells(Rows.Count,11).End(xlUp).Row
    qui permet de t'affranchir du nombre de cellules max (bien utile si la macro doit être utilisable avec d'autres versions d'Excel).

    Je ne comprends pas non plus à quand sert ton If puisque tu as déterminé un peu avant ta dernière cellule.
    Ou alors, il aurait mieux valu écrire :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    derligne = Sheets(ShtName).Cells(Rows.Count,11).End(xlUp).Row
    derligne = Min(derligne , Sheets(ShtName).Range("C5").End(xlDown).Row
    Ce qui permet de calculer ta limite une seule fois et évite de faire un test à chaque tour de ta boucle, ce qui ralentit ton code.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  13. #13
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    ok j'ai bien noté une partie des modifications à faire pour déterminer la dernière ligne et la dernière colonne d'ailleurs sur le même principe. puis la suppression du if contrôlant si la cellule est vide

    par contre comment transposer cette
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    Cells(a, dercolonn + 1).FormulaR1C1 = _
            "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"
    qui envoie la formule dans la cellule en
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    application.function.sumifs
    pour que ce soit la macro qui calcule ?

    j'en suis donc à ce code:
    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
    Sub glissant()
     
    Dim ShtName As String, NameFich As String, chemin As String
    Dim a As Long, derligne As Long
     
     
    chemin = Workbooks(ActiveWorkbook.Name).Path
    NameFich = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
     
    ActiveWorkbook.SaveAs Filename:= _
           chemin & "\" & NameFich & ".xlsm", FileFormat _
          :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
     
    ShtName = ActiveSheet.Name
    derligne = Sheets(ShtName).Cells(Rows.Count, 11).End(xlUp).Row
    dercolonn = Sheets(ShtName).Cells(1, Columns.Count).End(xlToLeft).Column
     
    choixsec = InputBox("Combien de secondes glissante (1 à 59) ? ", "Calcul glissant")
     
    For a = 5 To derligne
     
        Cells(a, dercolonn + 1).FormulaR1C1 = _
            "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"
     
    Next a
     
    MsgBox "Calcul effectué avec succès !", vbOKOnly
    ActiveWorkbook.Save
     
    End Sub
    en attendant un possible retour de votre part...merci et bonne soirée

  14. #14
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    j'ai tenté cette transposition mais cela bloque sur Year me disant que ) est attendu et je ne vois pas où ? d'une part et d'autres part je ne sais pas si le code est correct sans parler de la parenthèse !?


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    Cells(a, dercolonn + 1)=application.WorksheetFunction.SumIfs(range(cells(4,dercolonn),cells(a,dercolonn)),range(cells(4,2),cells(a,2)), ">=" & time(hour(cells(a,2)),minute(cells(a,2)),second(cells(a,2)) - (time(0,0,choixsec)) + (date(year(cells(a,2)),month(cells(a,2)),day(cells(a,2)))))

  15. #15
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    Cells(a, dercolonn+1) =application.WorksheetFunction.SumIfs _
    (range(cells(4,dercolonn),cells(a,dercolonn)), _
    range(cells(4,2),cells(a,2)), _
    ">=" & time(hour(cells(a,2)),minute(cells(a,2)),second(cells(a,2)) _
    - (time(0,0,choixsec)) _
    + (dateserial(year(cells(a,2)),month(cells(a,2)),day(cells(a,2)))))
    en passant de date à dateserial cela ne bloque plus par contre en lançant le code j'ai droit à un erreur d'exécution '13' Incompatibilité de type


    une idée svp ?

  16. #16
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    j'ai tenté cette transposition mais cela bloque sur Year me disant que ) est attendu et je ne vois pas où ? d'une part et d'autres part je ne sais pas si le code est correct sans parler de la parenthèse !?
    Je pense que ton erreur a été de traduire le fonction TEMPS d'Excel en Time de VBA qui n'ont pas du tout le même usage (Time sert à donner l'heure en cours). Il aurait fallu que tu utilises TimeSerial qui fonctionne comme TEMPS().

    Citation Envoyé par novice06 Voir le message
    en lançant le code j'ai droit à un erreur d'exécution '13' Incompatibilité de type
    Quand tu cliques sur Débugage, quelle ligne passe en surbrillance ?

    Citation Envoyé par novice06 Voir le message
    par contre comment transposer cette
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    Cells(a, dercolonn + 1).FormulaR1C1 = _
            "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"
    Pour garder le principe dont je parlais au début du sujet (de petites fonctions simples plutôt qu'une grosse fonction compliquées) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    Dim MaLigne as Long
     
    Cells(a, dercolonn + 1).Value = 0
    For MaLigne = 4 to a
       If DateDiff("s";Cells(MaLigne,2).Value,Cells(a,2).Value) <= 10 Then Cells(a, dercolonn + 1) = Cells(a, dercolonn + 1) + Cells(MaLigne,32).Value
    Next MaLigne
    A mettre à l'intérieur de ta boucle "For a= ... Next a", Excepté le Dim a mettre en début de code.

    Je ne l'ai pas testée, il se peut qu'il faille inverser le "a" et le MaLigne dans le test If.

    Mais on voit dans cet exemple que la fonction DateDiff de VBA peut grandement simplifier ta fonction, ce qui est un bon exemple pour illustrer ce que je disais dans mon premier message sur la transposition des fonctions Excel en VBA.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  17. #17
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    merci
    effectivement cette méthode est fonctionnelle par contre multiplie par 10 voire plus le temps de traitement


    en revenant à mon code et prenant en compte timeserial j'en suis à (en simplifiant un peu pour plus de visibilité)

    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
     
    Dim a As Long, derligne As Long, dercolonn As Long
    Dim criteretime As Double
     
    derligne = Sheets(ShtName).Cells(Rows.Count, 1).End(xlUp).Row
    dercolonn = Sheets(ShtName).Cells(1, Columns.Count).End(xlToLeft).Column
     
    choixsec = 10
     
    criteretime = TimeSerial(Hour(Cells(a, 2)), Minute(Cells(a, 2)), (Second(Cells(a, 2)) - choixsec)) + (DateSerial(Year(Cells(a, 2)), Month(Cells(a, 2)), Day(Cells(a, 2))))
     
     
    For a = 5 To derligne
     
    Cells(a, dercolonn + 1) = Application.WorksheetFunction.SumIfs _
    (Range(Cells(4, dercolonn), Cells(a, dercolonn)), _
    Range(Cells(4, 2), Cells(a, 2)), _
    ">=" & criteretime)
     
    Next a
    chacun des éléments contient en faisant pas à pas les bonne données mais le résultat obtenus est 0 à chaque calcul...il ne fait pas la somme ?!

  18. #18
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Citation Envoyé par novice06 Voir le message
    effectivement cette méthode est fonctionnelle par contre multiplie par 10 voire plus le temps de traitement
    Faire tous les calculs en parallèle est forcément plus rapide mais ça sature ta mémoire. Tu ne peux pas avoir quelque chose qui fait moins travailler la mémoire mais qui n'augmente pas le temps. C'est vouloir le beurre et l'argent du beurre.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  19. #19
    Membre régulier
    Inscrit en
    Février 2009
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Février 2009
    Messages : 188
    Points : 89
    Points
    89
    Par défaut
    l'une renvoi la formule dans la cellule et utilise énormément de ressources et de temps
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    Cells(a, dercolonn + 1).FormulaR1C1 = _
            "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"
    l'autre renvoi le résultat et utilise moins de ressources mais beaucoup plus de temps de traitement
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    If DateDiff("s";Cells(MaLigne,2).Value,Cells(a,2).Value) <= 10 Then Cells(a, dercolonn + 1) = Cells(a, dercolonn + 1) + Cells(MaLigne,32).Value
    dans les 2 cas le résultat final est positif en terme de valeur recherchée par le calcul mais le temps de traitement est considérable.

    application.worksheetFunction.sumifs est à priori ultra rapide comparé aux 2 précédente méthodes mais le tout est de savoir le configurer correctement surement ?! ce qui n'est apparemment pas mon cas !

  20. #20
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Dans mon code, je suis parti sur le principe de cumuler les valeurs dans la cellule elle-même.
    Je pense qu'en utilisant une variable pour faire les calculs intermédiaires, ça accélèrerait sans doute les choses.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    MonCalcul = 0
    For MaLigne = 4 to a
       If DateDiff("s";Cells(MaLigne,2).Value,Cells(a,2).Value) <= 10 Then MonCalcul = MonCalcul + Cells(MaLigne,32).Value
    Next MaLigne
    Cells(a, dercolonn + 1).Value = MonCalcul
    MonCalcul est à déclaré en Double
    Si tu as moins de 35000 lignes, tu peux déclarer MaLigne en Integer.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. formule somme.si.ens avec critére sur les dates
    Par AMEDDO79 dans le forum Excel
    Réponses: 15
    Dernier message: 10/02/2016, 11h35
  2. Somme.si.ens avec critères dynamiques
    Par eduardo9231 dans le forum Général VBA
    Réponses: 1
    Dernier message: 09/11/2014, 08h18
  3. [AC-2010] Requête avec critère sur date
    Par Psybo dans le forum Requêtes et SQL.
    Réponses: 2
    Dernier message: 14/06/2014, 00h19
  4. Requête Somme avec critère sur date
    Par Pixel dans le forum VBA Access
    Réponses: 1
    Dernier message: 26/05/2014, 21h02
  5. [XL-2010] Utilisation de somme.si.ens avec critère spécifique
    Par clem256 dans le forum Macros et VBA Excel
    Réponses: 13
    Dernier message: 06/01/2014, 16h53

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo