macro pour somme de cellules

[PHILIP]

Bonjour à tous,

J'ai un souci J'ai un tableau dont les données ont éte triées par date, dates se trouvant dans la colonne B. Chaque journée et leur données sont séparée d'une ligne vide inserée par code Ce que je souhaiterais c'est un code me permettant de faire la somme des données des colonnes C D et E dans la ligne vide Pourriez vous m'aider ?
ex
B C
18/09 14
18/09 13
ligne vide 27
17/09 15
17/09 18
17/09 7
ligne vide 40
etc


Merci pour votre aide

 

[ROGER2327]

Re : macro pour somme de cellules

Bonjour à tous

À job75 et à pierrejean :

Comme je le disais cette nuit, j'ai travaillé sur les procédures proposées, et je suis arrivé à ceci à partir de vos propositions :

[COLOR="DarkSlateGray"][B]Sub job75_2()
Dim n As Byte, a As Range, lc As Long
With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
On Error Resume Next 'si tableau vide...
n = 2 'n° colonne de départ
For Each a In Columns(n).SpecialCells(xlCellTypeFormulas, xlNumbers).Areas
  lc = a.Rows.Count
  Cells(a.Row + lc, n + 1).Resize(1, 3).FormulaR1C1 = "=SUM(R[" & -lc & "]C:R[-1]C)"
Next
With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub

Sub pierrejean_2()
Dim haut_somme&, n&
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  haut_somme = 4
  For n = 4 To Cells(Rows.Count, 1).End(xlUp).Row + 1
    If Range("A" & n) = "" Then
      Range(Cells(n, 3), Cells(n, 5)).FormulaR1C1 = "=SUM(R[" & haut_somme - n & "]C:R[-1]C)"
      haut_somme = n + 1
    End If
  Next
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub[/B][/COLOR]

La procédure job75_2 me donne du souci : elle fonctionne s'il n'y a pas trop de lignes vides, par exemple avec +/- 4000 lignes vides parmi +/- 28000 lignes. Mais elle échoue lorsque je double la taille de l'échantillon de données. Le problème semble être dans l'expression

Columns(n).SpecialCells(xlCellTypeFormulas, xlNumbers).Areas​

Quelqu'un a-t-il une idée sur les éventuelles limites de cette expression ?​

ROGER2327
#4188

Vendredi 20 Absolu 138 (Sainte Mélusine, souillarde de cuisine, SQ)
6 Vendémiaire An CCXIX
2010-W39-1T10:10:38Z

 

[laetitia90]

Re : macro pour somme de cellules

bonjour tous
specialcells je crois bien pas sur !!! qu'on est limite a 8192 cellules vide contiguees

 

[job75]

Re : macro pour somme de cellules

Bonjour Roger, laetitia,

Vous avez bien vu les posts #12 à 15 n'est-ce pas ?

En tout cas j'ai testé les 2 macros présentées par Roger :

Sub sommes() 'Job75_2
Dim t#, n As Byte, a As Range, lc As Long
t = Timer
With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
On Error Resume Next 'si tableau vide...
n = 2 'n° colonne de départ
For Each a In Columns(n).SpecialCells(xlCellTypeConstants, xlNumbers).Areas
  lc = a.Rows.Count
  Cells(a.Row + lc, n + 1).Resize(1, 3).FormulaR1C1 = "=SUM(R[" & -lc & "]C:R[-1]C)"
Next
With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
MsgBox Timer - t
End Sub

Durée 17 s, toujours pas terrible !

Sub sommes() 'pierrejean_2
Dim t#, haut_somme&, n&
t = Timer
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  haut_somme = 4
  For n = 4 To Cells(Rows.Count, 1).End(xlUp).Row + 1
    If Range("A" & n) = "" Then
      Range(Cells(n, 3), Cells(n, 5)).FormulaR1C1 = "=SUM(R[" & haut_somme - n & "]C:R[-1]C)"
      haut_somme = n + 1
    End If
  Next
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
MsgBox Timer - t
End Sub

Durée => 6,34 s

A+

 

[laetitia90]

Re : macro pour somme de cellules

rebonjour
Job j'ai pas suivi le post je repondais seulement a Roger concernant

Columns(n).SpecialCells(xlCellTypeFormulas, xlNumbers).Areas

en cherchant sur le net j'ai trouve cela si ca peut aider

Lorsque vous créez un Microsoft macro Visual Basic pour Applications (VBA) qui sélectionne plusieurs plages non contiguës dans un classeur qui utilise une expression VBA qui est similaire aux actions suivantes, qui étaient censés uniquement pour se produire avec les cellules non contiguës viennent à chaque cellule de la sélection d'origine de la feuille de calcul Microsoft Excel :

expression.SpecialCells(XlCellType).expression XlCellType

peut être l'une des opérations suivantes :

* xlCellTypeAllFormatConditions
* xlCellTypeAllValidation
* xlCellTypeBlanks
* xlCellTypeComments
* xlCellTypeConstants
* xlCellTypeFormulas
* xlCellTypeSameFormatConditions
* xlCellTypeSameValidation
* xlCellTypeVisible


Ce problème se produit si vous sélectionnez plusieurs 8,192 cellules non contiguës avec votre macro. Excel prend en charge uniquement un maximum de 8,192 cellules non contiguës par des macros VBA.

En règle générale, si vous essayez de sélectionner manuellement 8,192 plus de cellules non contiguës, vous recevez le message d'erreur suivantes :
La sélection est trop grande.
Toutefois, lorsque vous utilisez une macro VBA pour la même ou une sélection semblable, sans message d'erreur est déclenché et aucun code d'erreur n'est généré qui peuvent être capturées par un gestionnaire d'erreurs.

 

[pierrejean]

Re : macro pour somme de cellules

Re

Bises Laetitia et merci pour le tuyau

@ Job

Curieux !!!
chez moi la macro la plus veloce est la pierrejean2 qui devrait se nommer Rogerxxxx du nom de son veritable auteur
Jusqu'a present ta machine me paraissait plus puissante que la mienne , mais la elle semble se fatiguer !!!
Comment expliquer ?

 

[ROGER2327]

Re : macro pour somme de cellules

Re..

bonjour tous
specialcells je crois bien pas sur !!! qu'on est limite a 8192 cellules vide contiguees

Merci laetitia90, pour ce message comme pour le suivant. Je subodorais une explication de ce genre, mais je ne l'avais pas trouvée. C'est le genre de limitations qu'on aurait aimé voir éliminé avant de passer la taille des feuilles de 65000 lignes à un million...
​

ROGER2327
#4189

Vendredi 20 Absolu 138 (Sainte Mélusine, souillarde de cuisine, SQ)
6 Vendémiaire An CCXIX
2010-W39-1T13:04:39Z

 

[job75]

Re : macro pour somme de cellules

Re,

Curieux !!!
chez moi la macro la plus veloce est la pierrejean2 (...)

Curieux en effet puiqu'une boucle est supprimée (mais on est alors en R1C1...).

Je constate que chez moi les durées d'exécution sont parfois quelque peu aléatoires.

Mais j'ai bien retesté les macros des posts 14 et 15 : elles sont plus rapides.

A+

 

[ROGER2327]

Re : macro pour somme de cellules

Re...

Re

Bises Laetitia et merci pour le tuyau

@ Job

Curieux !!!
chez moi la macro la plus veloce est la pierrejean2 qui devrait se nommer Rogerxxxx du nom de son veritable auteur
Jusqu'a present ta machine me paraissait plus puissante que la mienne , mais la elle semble se fatiguer !!!
Comment expliquer ?

Fort intéressant ! Côté rapidité, j'arrive à la même constatation que vous. Mais ces procédures sont étonnantes car en répétant l'essai sur le même échantillon, les durées d'exécution varient beaucoup.

Quant à la dénomination pierrejean_2, elle est logique car je reprends votre structure. J'ai seulement essayé d'utiliser une formule type LC pour éviter la boucle sur les trois cellules.

Si notre ami s'inquiète un jour des suites de sa demande, il me semble que votre solution devrait retenir son attention. Le principe de celle donnée par job75 m'intéresse beaucoup, mais les précisions données par laetitia90 indiquent que son utilisation doit se faire avec précaution.​

ROGER2327
#4190

Vendredi 20 Absolu 138 (Sainte Mélusine, souillarde de cuisine, SQ)
6 Vendémiaire An CCXIX
2010-W39-1T13:19:13Z

 

[job75]

Re : macro pour somme de cellules

Re,

Désolé Roger, nos posts 22 et 23 se sont croisés.

Bien d'accord avec vous, j'ajouterai seulement qu'Excel 2003 et 2007 ne se comportent pas tout à fait de la même manière.

A+

 

[ROGER2327]

Re : macro pour somme de cellules

Re-bonjour

Reprenant mon idée de départ (i.e. pas d'ajout de formule), mais sans écraser les formules existantes :

[COLOR="DarkSlateGray"][B]Sub ROGER2327_3b()
Dim i&, j&, oDat, sDat(1 To 1, 3 To 5)
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  oDat = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp).Offset(1, 4)).Value
  For i = 4 To UBound(oDat, 1)
    If Not IsEmpty(oDat(i, 1)) Then
      For j = 3 To 5: sDat(1, j) = sDat(1, j) + oDat(i, j): Next
    Else
      Cells(i, 3).Resize(1, 3) = sDat
      Erase sDat
    End If
  Next
  Erase oDat
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub[/B][/COLOR]

Pour ajouter les formules de sommation (sans, bien sûr, écraser les formules existantes) :

[COLOR="DarkSlateGray"][B]Sub ROGER2327_2c()
Dim i&, n&
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  n = 4
  For i = n To Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If IsEmpty(Cells(i, 3)) Then
      Cells(i, 3).Resize(1, 3).FormulaR1C1 = "=SUM(R[" & n - i & "]C:R[-1]C)"
      n = i + 1
    End If
  Next i
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub[/B][/COLOR]

qui est, à quelques détails d'écriture près, identique à la version pierrejean_2.

Sur ce, je laisse la question de côté pour l'instant, faute de trouver des choses plus rapides.

Remarque : Chez moi, ROGER2327_3b est (à la louche) deux fois plus rapide que ROGER2327_2c (sur les mêmes données truffées de formules, bien sûr...) Autrement dit, l'adjonction de formules coûte cher_!​

ROGER2327
#4191

Vendredi 20 Absolu 138 (Sainte Mélusine, souillarde de cuisine, SQ)
6 Vendémiaire An CCXIX
2010-W39-1T16:17:03Z

 

[ROGER2327]

Re : macro pour somme de cellules

...et bonne nuit !

Pour conclure, je dépose mon classeur de test qui regroupe onze variantes. Ça n'a pas grand intérêt, mais on n'a pas cogité pour rien...

Cordialement,​

ROGER2327
#4198

Samedi 21 Absolu 138 (Saint Venceslas, duc, SQ)
7 Vendémiaire An CCXIX
2010-W39-2T00:27:26Z

 

[PHILIP]

Re : macro pour somme de cellules

Bonjour à tous,

Revenu de déplacement je tenais à remercier Job 75, Pierrejean, Roger2327,
Nat54 et Leaticia90 pour leurs investissements, j'ai pris une sacrée leçon

Bon AP

PHILIP