Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 Ecriture de formules dans VBA sur cellules non vides

ShuarS

XLDnaute Occasionnel
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
 

Pièces jointes

  • TEST_LP.xlsx
    9 KB · Affichages: 24
Solution
S
@ShuarS, le fil,

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

VB: 
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...
Trier par date Trier par votes

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Shuars,Job,
Pourquoi donc une macro ?
En Y :
VB: 
=SOMME.SI($B2:$B3000;"*";D2:D3000)
En X :
Code: 
=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.
 

Pièces jointes

  • TEST_LP.xlsx
    8.9 KB · Affichages: 3
Vote positif 0 Vote négatif
S

soan

XLDnaute Barbatruc
Inactif
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
 

Pièces jointes

  • TEST_LP.xlsx
    9.8 KB · Affichages: 2
Vote positif 0 Vote négatif

ShuarS

XLDnaute Occasionnel
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
 
Vote positif 0 Vote négatif
S

soan

XLDnaute Barbatruc
Inactif
@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.

VB: 
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
 

Pièces jointes

  • TEST_LP.xlsm
    16.4 KB · Affichages: 4
Vote positif 0 Vote négatif
D

danielco

XLDnaute Accro
Essaie :
VB: 
Sub TEST()
  [B:B].SpecialCells(xlCellTypeConstants) _
  .SpecialCells(xlCellTypeConstants).Offset(, 6).FormulaR1C1 = "=SUM(RC4,RC6)"
End Sub
Daniel
 
Vote positif 0 Vote négatif

ShuarS

XLDnaute Occasionnel
@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 !
 
Vote positif 0 Vote négatif

ShuarS

XLDnaute Occasionnel
@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
 

Pièces jointes

  • TEST_LP2.xlsx
    8.9 KB · Affichages: 5
Vote positif 0 Vote négatif
D

danielco

XLDnaute Accro
Désolé :
VB: 
Sub TEST()
  [B:B].SpecialCells(xlCellTypeConstants) _
  .SpecialCells(xlCellTypeConstants).Offset(, 6).FormulaR1C1 = "=SUM(RC4:RC6)"
End Sub
Daniel
 
Vote positif 0 Vote négatif
S

soan

XLDnaute Barbatruc
Inactif
@ShuarS, le fil,

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

VB: 
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
 

Pièces jointes

  • TEST_LP2.xlsm
    16.3 KB · Affichages: 5
Vote positif 0 Vote négatif
Solution
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

S
  • Question
XL 2016 Caractère fantôme invisible dans cellule vide non vide.
Réponses
5
Affichages
301
SurfingJoe
S
W
  • Question
Microsoft 365 Fusionne les cellules vides parmi celle avec des valeurs
Réponses
4
Affichages
439
woira95
W
B
  • Résolu(e)
Microsoft 365 Problème dans formule VBA
Réponses
7
Affichages
200
BOILEAU
B
P
  • Question
Microsoft 365 Addition de cellules vides et non vides
Réponses
11
Affichages
651
Droopyougo
M
  • Question
Microsoft 365 trouver la dernière valeur numérique non nulle dans une colonne comportant des cellules vides et des cellules avec la valeur zéro.
Réponses
5
Affichages
848
matgrim
M
J
  • Question
XL 2016 Nombre de lignes différentes destination/données
Réponses
7
Affichages
318
Jakes
J
O
  • Résolu(e)
Microsoft 365 Effacer le contenu de cellules si une autre cellule est vide
Réponses
9
Affichages
222
Olihoes
O
  • Résolu(e)
XL 2021 Supprimer les lignes qui contiennent des cellules vides dans les colonnes "A à M" à partir de la ligne 5
Réponses
6
Affichages
579
Usine à gaz
M
  • Question
Microsoft 365 Indice de révision de chaque ligne automatiquement
Réponses
2
Affichages
177
MLIZIN
M
O
  • Question
XL 2019 erreur de valeur sur formule
2
Réponses
18
Affichages
794
olivier1984
O
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…