XL 2016 Amélioration Vba

Guismo33 · 10 Mars 2019

Bonjour a tous,

je me suis fait une petite Vba "Private" et je voudrais savoir si on pourrait l'améliorer svp .

Option Explicit

Const plage = "D1214,D1618,D21", plage2 = "g12:G14,G16:G18,G21"
Const plage3 = "D19,D2224", plage4 = "G19,G22:G24"
Const plage5 = "D10,D15", plage6 = "G10,G15", plage7 = "D11", plage8 = "G11"
Const celsom = "D26", celsom2 = "g26"
Const celsom3 = "D27", celsom4 = "G27"
Const celsom5 = "D29", celsom6 = "G29", celsom7 = "D30", celsom8 = "G30"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, b, c, d, e, f, g, h

If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Not Intersect(Target, Range(plage2)) Is Nothing Then
b = Application.WorksheetFunction.Sum(Range(plage2))
Range(celsom2).Value = b
d = Application.WorksheetFunction.Sum(Range(plage4))
Range(celsom4).Value = d
f = Application.WorksheetFunction.Sum(Range(plage6))
Range(celsom6).Value = f
h = Application.WorksheetFunction.Sum(Range(plage8))
Range(celsom8).Value = h
a = Application.WorksheetFunction.Sum(Range(plage))
Range(celsom).Value = a
c = Application.WorksheetFunction.Sum(Range(plage3))
Range(celsom3).Value = c
e = Application.WorksheetFunction.Sum(Range(plage5))
Range(celsom5).Value = e
g = Application.WorksheetFunction.Sum(Range(plage7))
Range(celsom7).Value = g
End If

End Sub

en vous remerciant par avance .

bien à vous

job75 · 10 Mars 2019

Bonjour Guismo33,

Avec des variables tableau c'est à peu près évident :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim plage$(1 To 8), celsom$(1 To 8), n
plage(1) = "D12:D14,D16:D18,D21": plage(2) = "G12:G14,G16:G18,G21"
plage(3) = "D19,D22:D24": plage(4) = "G19,G22:G24"
plage(5) = "D10,D15": plage(6) = "G10,G15"
plage(7) = "D11": plage(8) = "G11"
celsom(1) = "D26": celsom(2) = "G26"
celsom(3) = "D27": celsom(4) = "G27"
celsom(5) = "D29": celsom(6) = "G29"
celsom(7) = "D30": celsom(8) = "G30"
If Target.Count > 1 Or Target(1) = "" Or Intersect(Target, Range(plage(2))) Is Nothing Then Exit Sub
For n = 1 To UBound(plage)
    Range(celsom(n)) = Application.Sum(Range(plage(n)))
Next
End Sub

A+

Guismo33 · 11 Mars 2019

job75 à dit:

Bonjour Guismo33,

Avec des variables tableau c'est à peu près évident :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim plage$(1 To 8), celsom$(1 To 8), n
plage(1) = "D12:D14,D16:D18,D21": plage(2) = "G12:G14,G16:G18,G21"
plage(3) = "D19,D22:D24": plage(4) = "G19,G22:G24"
plage(5) = "D10,D15": plage(6) = "G10,G15"
plage(7) = "D11": plage(8) = "G11"
celsom(1) = "D26": celsom(2) = "G26"
celsom(3) = "D27": celsom(4) = "G27"
celsom(5) = "D29": celsom(6) = "G29"
celsom(7) = "D30": celsom(8) = "G30"
If Target.Count > 1 Or Target(1) = "" Or Intersect(Target, Range(plage(2))) Is Nothing Then Exit Sub
For n = 1 To UBound(plage)
    Range(celsom(n)) = Application.Sum(Range(plage(n)))
Next
End Sub

A+

Bonjour,

merci job,débutant et en plus avec code "Private" pas évident.

bien à vous

XL 2016 Amélioration Vba

Guismo33

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Guismo33

XLDnaute Occasionnel

Discussions similaires