Microsoft 365 Ecriture de formules dans VBA sur cellules non vides

[ShuarS]

Bonjour à tous,

Meilleurs Vœux 2021 !

Je n’ai pas fait de VBA depuis un moment et je coince sur l’écriture du code.

Je joins un fichier pour modèle.
La seule particularité est le nombre de cellules non vides en colonne B.
Ces cellules peuvent être variables en quantité et en ligne.
C.-à-d., non vide en B3 et B5 dans mon exemple, mais dans un autre fichier ce pourrait être B12 et B20.

L’idée est donc dans un premier temps d’identifier les cellules non vides de la colonne B.
Ensuite j’aimerais en D1 ; E1 ; F1 écrire au lancement de la macro une formule de SOMME sur les ces colonnes et lignes non vides.
Pour finir en colonne H, j’aimerais écrire une SOMME des lignes non vides.

J’espère avoir été clair,
Merci pour votre aide.

Shu

 

[job75]

Bonjour ShuarS,

Pour l'instant je ne vois pas l'intérêt de votre question puisque les cellules vides, tout le monde le sait, ne modifient pas le résultat de la fonction SOMME.

A+

 

[sylvanu]

Bonjour Shuars,Job,
Pourquoi donc une macro ?
En Y :

=SOMME.SI($B2:$B3000;"*";D2:D3000)

En X :

=SI(B3<>"";SOMME(D3:F3);"")

Comme en PJ, j'ai supposé que des lignes pouvait être non vides avec en B des cellules vides.
Sinon ça ne sert à rien, des sommes sont suffisantes.

 

[danielco]

Bonjour,

Essaie :

Sub TEST()
  [B:B].SpecialCells(xlCellTypeConstants).FormulaR1C1 = "=SUM(RC[2],RC[4])"
End Sub

(la première formule étant =SOMME(D3:F3.)
Sinon, mettre R[-2] au lieu de R

Daniel

 

[soan]

Bonjour,

formule en D1 (déjà tirée vers la droite jusqu'en F1) :

=SOMME.SI($B3:$B5000;"<>";D3:D5000)

formule en H3 (déjà tirée vers le bas jusqu'en H30) :

=SI(B3="";"";SOMME(D3:F3))

soan

 

[ShuarS]

Merci pour vos retours !

Je me suis sans doute mal exprimé.
L'idée est d'intégrer ces formules au format VBA dans un code plus grand.
La réponse de @danielco semble correspondre à mon besoin.

Concernant le choix des cellules, l'idée est d'identifier les cellules non vides de la colonne B pour afficher le résultat de la somme des lignes correspondantes et d'ignorer les autres.
EX.: Si B3 est non vide alors j'écris en H3 la somme des cellules D3+E3+F3
Si B4 est vide, je passe.

Merci pour votre aide une fois de plus.

Shu

 

[soan]

@ShuarS, le fil,

oooppppsss ! j'ai zappé que tu demandais une solution en VBA !

c'est fait dans le fichier joint ; fais Ctrl e ➯ travail effectué !

j'ai utilisé la méthode des tableaux ➯ le job sera fait très rapidement,
même pour plusieurs milliers de lignes.

Option Explicit

Sub Essai()
  Dim dlg&: dlg = Cells(Rows.Count, 2).End(3).Row: If dlg = 1 Then Exit Sub
  Dim Tbl, T(3), lig&, col%: Tbl = [B1].Resize(dlg, 7)
  For lig = 2 To dlg
    If Tbl(lig, 1) <> "" Then
      T(0) = 0
      For col = 3 To 5
        T(0) = T(0) + Tbl(lig, col)
        T(col - 2) = T(col - 2) + Tbl(lig, col)
      Next col
      Tbl(lig, 7) = T(0)
    End If
  Next lig
  Application.ScreenUpdating = 0: [D1] = T(1): [E1] = T(2): [F1] = T(3)
  [H1].Resize(dlg) = Application.Index(Tbl, Evaluate("Row(" & "1:" & dlg & ")"), 7)
End Sub

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis.

soan

 

[danielco]

Merci pour vos retours !

Je me suis sans doute mal exprimé.
L'idée est d'intégrer ces formules au format VBA dans un code plus grand.
La réponse de @danielco semble correspondre à mon besoin.

Concernant le choix des cellules, l'idée est d'identifier les cellules non vides de la colonne B pour afficher le résultat de la somme des lignes correspondantes et d'ignorer les autres.
EX.: Si B3 est non vide alors j'écris en H3 la somme des cellules D3+E3+F3
Si B4 est vide, je passe.

Merci pour votre aide une fois de plus.

Shu

Essaie :

Sub TEST()
  [B:B].SpecialCells(xlCellTypeConstants) _
  .SpecialCells(xlCellTypeConstants).Offset(, 6).FormulaR1C1 = "=SUM(RC4,RC6)"
End Sub

Daniel

 

[ShuarS]

@soan
Wouaaaa !

Je n'ai pas une connaissance très poussée du VBA mais je ne crois avoir déjà vu du code comme le tien.
En effet c'est vraiment très efficace !
La lecture n'est pas évidente je vais peut être avoir du mal pour l'adapter alors oui j'essaie et je te dis.

@danielco
Merci encore.
Je teste également ta solution proposée et je reviens te dire.

Au top !

 

[ShuarS]

@soan
Je suis perdu dans le code.
Il me semble lire que tu nommes une plages de données dans laquelle tu te déplaces pour calculer mais je ne suis pas très à l'aise avec ce code...

@danielco
Tes lignes fonctionnent bien merci.
Par contre la somme ne prend pas en compte tous les chiffres (seulement D et F dans mon premier ex.).
Je dois rajouter des "RC" dans la formule <=SUM(RC4,RC6)> ?


Je joins un second fichier modèle avec les placements corrects pour les résultats.
Pour info le nombre de ligne de la colonne B sera variable mais je crois que vous l'aviez déjà pris en compte.

Shu

 

[danielco]

@soan
Je suis perdu dans le code.
Il me semble lire que tu nommes une plages de données dans laquelle tu te déplaces pour calculer mais je ne suis pas très à l'aise avec ce code...

@danielco
Tes lignes fonctionnent bien merci.
Par contre la somme ne prend pas en compte tous les chiffres (seulement D et F dans mon premier ex.).
Je dois rajouter des "RC" dans la formule <=SUM(RC4,RC6)> ?


Je joins un second fichier modèle avec les placements corrects pour les résultats.
Pour info le nombre de ligne de la colonne B sera variable mais je crois que vous l'aviez déjà pris en compte.

Shu

Désolé :

Sub TEST()
  [B:B].SpecialCells(xlCellTypeConstants) _
  .SpecialCells(xlCellTypeConstants).Offset(, 6).FormulaR1C1 = "=SUM(RC4:RC6)"
End Sub

Daniel

 

[ShuarS]

@danielco
J'aurais dû le voir ! Merci pour la correction.
Deux petites lignes qui m'arrangent bien !
J'ai pu adapter ce code pour mon fichier merci

 

[danielco]

Juste un bémol. Il ne fonctionnerait pas tel quel s'il y avait des formules en colonne B. Il faudrait alors le modifier.
Daniel

 

[soan]

@ShuarS, le fil,

ton fichier en retour ; même utilisation : Ctrl e

Option Explicit

Sub Essai()
  Dim dlg&: dlg = Cells(Rows.Count, 2).End(3).Row: If dlg < 3 Then Exit Sub
  Dim Tbl, T(5), lig&, col%: dlg = dlg - 2: Tbl = [B3].Resize(dlg, 17)
  For lig = 1 To dlg
    If Tbl(lig, 1) <> "" Then
      T(0) = 0
      For col = 3 To 7
        T(0) = T(0) + Tbl(lig, col)
        T(col - 2) = T(col - 2) + Tbl(lig, col)
      Next col
      Tbl(lig, 17) = T(0)
    End If
  Next lig
  Application.ScreenUpdating = 0: Columns(18).ClearContents
  For col = 4 To 8
    Cells(2, col) = T(col - 3)
  Next col
  [R3].Resize(dlg) = Application.Index(Tbl, Evaluate("Row(" & "1:" & dlg & ")"), 17)
End Sub

effectivement, j'reconnais qu'l'adaptation était pas facile !
j'ai aussi ajouté un effacement préalable de la colonne R.

soan

 

[ShuarS]

@soan , excellent !

Merci bcp à vous

Arf je ne peux enregistrer qu'une solution ^^ dommage !