Equations to codes

Version imprimable

09/08/2013, 07h10
alidroos

1 pièce(s) jointe(s)

Equations to codes

Bonjour

Je veux codes pour les équations suivantes

Code:

=IF(A12<>A13;SUMIF($A$11:A12;A12;$C$11:D12);"--")

Code:

=IF(A12<>A13;SUMIF($A$11:A12;A12;$B$11:E12);"--")

Code:

=IF(A12="";"";SUBTOTAL(3;$A$11:$A12))

Fichier attaché explique mon plus
Colonnes colorées équations
09/08/2013, 10h16
Daranc

Tu traduis avec Google?
parce que tu veux le code VBA ? puisque les formules sont dans la feuille
You translate with Google?
because you want the VBA code? since the formulas in the worksheet
يمكنك ترجمة مع جوجل؟
لأنك تريد رمز VBA؟ منذ الصيغ في ورقة العمل

Bonjour Daranc

Je tiens à le transformer en code

Je serai reconnaissant à vous

Code:

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
Public Const M As Integer = 1
Public Const E As Integer = 7
Public Const V As Integer = 13
Public Sub Frm_A(R As Integer, C As Integer)
Dim Rng As Range
Dim Itm
'=IF(A12<>A13;SUMIF($A$11:A12;A12;$C$11:D12);"--")
'=IF(A12<>A13;SUMIF($A$11:A12;A12;$B$11:E12);"--")
'=IF(A12="";"";SUBTOTAL(3;$A$11:$A12))
Itm = C
For Each Rng In Range(Cells(12, C), Cells(R, C))
With Rng
If .Row >= 12 Then
Select Case Itm
'===========================
       Case Is = M
'===========================
Select Case .Value
       Case Not .Offset(1, 0).Value
 .Offset(0, 3) = Application.SumIf(Range(Cells(.Row, .Column).Address, Cells(.Row + 1, .Column)), _
 Cells(.Row + 1, .Column), Range(Cells(.Row, .Column + 2).Address, Cells(.Row + 1, .Column + 3)))
       Case Else
 .Offset(0, 3) = "--"
End Select
'-------------------
Select Case .Value
       Case Not .Offset(1, 0).Value
 .Offset(0, 4) = Application.SumIf(Range(Cells(.Row, .Column).Address, Cells(.Row + 1, .Column)), _
 Cells(.Row + 1, .Column), Range(Cells(.Row, .Column + 1).Address, Cells(.Row + 1, .Column + 4)))
       Case Else
 .Offset(0, 3) = "--"
End Select
'-------------------
Select Case Cells(R, C)
       Case Is = ""
       .Offset(0, 4) = ""
       Case Else
 .Offset(0, 5) = Application.Subtotal(3, Range(Cells(.Row - 1, .Column).Address, Cells(.Row + 1, .Column).Address(0, 1)))
End Select
'===========================
    Case Is = E
'===========================
Select Case .Value
       Case Not .Offset(1, 0).Value
 .Offset(0, 3) = Application.SumIf(Range(Cells(.Row, .Column).Address, Cells(.Row + 1, .Column)), _
 Cells(.Row + 1, .Column), Range(Cells(.Row, .Column + 2).Address, Cells(.Row + 1, .Column + 3)))
       Case Else
 .Offset(0, 3) = "--"
End Select
'-------------------
Select Case .Value
       Case Not .Offset(1, 0).Value
 .Offset(0, 4) = Application.SumIf(Range(Cells(.Row, .Column).Address, Cells(.Row + 1, .Column)), _
 Cells(.Row + 1, .Column), Range(Cells(.Row, .Column + 1).Address, Cells(.Row + 1, .Column + 4)))
       Case Else
 .Offset(0, 3) = "--"
End Select
'-------------------
Select Case Cells(R, C)
       Case Is = ""
       .Offset(0, 4) = ""
       Case Else
 .Offset(0, 5) = Application.Subtotal(3, Range(Cells(.Row - 1, .Column).Address, Cells(.Row + 1, .Column).Address(0, 1)))
End Select
'===========================
    Case Is = V
'===========================
Select Case .Value
       Case Not .Offset(1, 0).Value
 .Offset(0, 3) = Application.SumIf(Range(Cells(.Row, .Column).Address, Cells(.Row + 1, .Column)), _
 Cells(.Row + 1, .Column), Range(Cells(.Row, .Column + 2).Address, Cells(.Row + 1, .Column + 3)))
       Case Else
 .Offset(0, 3) = "--"
End Select
'-------------------
Select Case .Value
       Case Not .Offset(1, 0).Value
 .Offset(0, 4) = Application.SumIf(Range(Cells(.Row, .Column).Address, Cells(.Row + 1, .Column)), _
 Cells(.Row + 1, .Column), Range(Cells(.Row, .Column + 1).Address, Cells(.Row + 1, .Column + 4)))
       Case Else
 .Offset(0, 3) = "--"
End Select
'-------------------
Select Case Cells(R, C)
       Case Is = ""
       .Offset(0, 4) = ""
       Case Else
 .Offset(0, 5) = Application.Subtotal(3, Range(Cells(.Row - 1, .Column).Address, Cells(.Row + 1, .Column).Address(0, 1)))
End Select
'===========================
End Select
End If
End With
Next Rng
End Sub

Tentative ratée
Je ne sais pas où est le mal
S'il vous plaît aidez-moi de corriger le code

10/08/2013, 14h05
Daranc

Reprenons!
tu veux que le code fasse ce que fait le formules?
comment apportes tu les données dans la feuille ? une importation ou des saisies au jour le jour
PS: le codage VBA que tu as fait , je ne le comprends pas :calim2:
donc savoir ce que tu veux faire me permettra de voir comment approcher la codification

pourquoi

Code:

=SI(A18<>A19;SOMME.SI($A$11:A18;A18;$C$11:D18);"--")

alors que ceci

Code:

=SI(A18<>A19;C18;"--")

fait la même chose ?
je suis désolé mais sans savoir ce que doit faire le programme , j'aurais du mal à t'aider

Bonjour Daranc
Après les tentatives de parvenir à une solution

Code:

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
Public Const M As Integer = 1
Public Const E As Integer = 7
Public Const V As Integer = 13
Public La, Bb
Public Sub Dr()
With Cells(Rows.Count, Bb).End(xlUp)
   Range(Cells(11, .Column), Cells(.Row, .Column)).Offset(0, 3).Resize(, 3).ClearContents
   Frm_A .Row, .Column
End With
End Sub
Public Sub Frm_A(ByRef R As Integer, ByRef Cl As Integer)
Dim Rng As Range
Dim Itm
Itm = Cl
Tre_F 0
For Each Rng In Range(Cells(12, Cl), Cells(R, Cl))
If Rng.Row = 10 Then GoTo 0
Select Case Itm
       Case Is = M
        A Rng.Offset(0, 3)
       Case Is = E
        B Rng.Offset(0, 3)
       Case Is = V
        C Rng.Offset(0, 3)
End Select
Next
Tre_F 1
Exit Sub
0:
With Cells(R, Cl)
Select Case Itm
       Case Is = M
        Al_F Rng, M
       Case Is = E
        Al_F Rng, E
       Case Is = V
        Al_F Rng, V
End Select
End With
End Sub
Private Function A(Rng As Range) As Currency
Dim R As Range
Dim Id
Dim Ar As Variant
Ar = Array("=IF(RC[-3]<>R[1]C[-3],SUMIF(R11C1:RC[-3],RC[-3],R11C3:RC),""--"")", _
          "=IF(RC[-4]<>R[1]C[-4],SUMIF(R11C1:RC[-4],RC[-4],R11C2:RC),""--"")", _
          "=IF(RC[-5]="""","""",SUBTOTAL(3,R11C1:RC1))")
Tre_F 0
For Each R In Rng.Resize(5)
 With R
  .FormulaR1C1 = Ar(0)
  .Offset(, 1).FormulaR1C1 = Ar(1)
  .Offset(, 2).FormulaR1C1 = Ar(2)
' .Value = .Value2: .Offset(0, 1) = .Offset(0, 1).Value2: .Offset(0, 2) = .Offset(0, 2).Value2
 End With
Next
Tre_F 1
End Function
Private Function B(Rng As Range) As Currency
Dim R As Range
Dim Id
Dim Ar As Variant
Ar = Array("=IF(RC[-3]<>R[1]C[-3],SUMIF(R11C7:RC[-3],RC[-3],R11C9:RC),""--"")", _
          "=IF(RC[-4]<>R[1]C[-4],SUMIF(R11C7:RC[-4],RC[-4],R11C8:RC),""--"")", _
          "=IF(RC[-5]="""","""",SUBTOTAL(3,R11C7:RC7))")
Tre_F 0
For Each R In Rng.Resize(5)
 With R
  .FormulaR1C1 = Ar(0)
  .Offset(0, 1).FormulaR1C1 = Ar(1)
  .Offset(0, 2).FormulaR1C1 = Ar(2)
' .Value = .Value2: .Offset(0, 1) = .Offset(0, 1).Value2: .Offset(0, 2) = .Offset(0, 2).Value2
 End With
Next
Tre_F 1
End Function
Private Function C(Rng As Range) As Currency
Dim R As Range
Dim Id
Dim Ar  As Variant
Ar = Array("=IF(RC[-3]<>R[1]C[-3],SUMIF(R11C13:RC[-3],RC[-3],R11C15:RC),""--"")", _
          "=IF(RC[-4]<>R[1]C[-4],SUMIF(R11C13:RC[-4],RC[-4],R11C14:RC),""--"")", _
          "=IF(RC[-5]="""","""",SUBTOTAL(3,R11C13:RC13))")
Tre_F 0
For Each R In Rng.Resize(5)
 With R
 .FormulaR1C1 = Ar(0)
  .Offset(0, 1).FormulaR1C1 = Ar(1)
  .Offset(0, 2).FormulaR1C1 = Ar(2)
' .Value = .Value2: .Offset(0, 1) = .Offset(0, 1).Value2: .Offset(0, 2) = .Offset(0, 2).Value2
 End With
Next
Tre_F 1
End Function
Private Function Al_F(Rng As Range, Inx) As Currency
Dim Ar, Arr, Ar2 As Variant
Dim Cnt
Ar = Array("=IF(RC[-3]<>R[1]C[-3],SUMIF(R11C1:RC[-3],RC[-3],R11C3:RC),""--"")", _
          "=IF(RC[-4]<>R[1]C[-4],SUMIF(R11C1:RC[-4],RC[-4],R11C2:RC),""--"")", _
          "=IF(RC[-5]="""","""",SUBTOTAL(3,R11C1:RC1))")
Arr = Array("=IF(RC[-3]<>R[1]C[-3],SUMIF(R11C7:RC[-3],RC[-3],R11C9:RC),""--"")", _
          "=IF(RC[-4]<>R[1]C[-4],SUMIF(R11C7:RC[-4],RC[-4],R11C8:RC),""--"")", _
          "=IF(RC[-5]="""","""",SUBTOTAL(3,R11C7:RC7))")
Ar2 = Array("=IF(RC[-3]<>R[1]C[-3],SUMIF(R11C13:RC[-3],RC[-3],R11C15:RC),""--"")", _
          "=IF(RC[-4]<>R[1]C[-4],SUMIF(R11C13:RC[-4],RC[-4],R11C14:RC),""--"")", _
          "=IF(RC[-5]="""","""",SUBTOTAL(3,R11C13:RC13))")
Tre_F 0
Cnt = Inx
For Offc = 0 To 2
With Rng.Offset(1, Offc + 3)
 .FormulaR1C1 = IIf(Cnt = M, Ar(Offc), IIf(Cnt = E, Arr(Offc), Ar2(Offc)))
' .Value = .Value2
End With
Next
Tre_F 1
End Function
Private Function Tre_F(B As Boolean)
With Application
   .Calculation = IIf(B = 1, -4105, -4135)
   .EnableEvents = B
   .ScreenUpdating = B
End With
End Function

Nous vous remercions de votre collaboration avec moi