Votre question

Comment faire deux code en un seul

Tags :
  • Programmation
Dernière réponse : dans Programmation
21 Avril 2017 13:18:33

Bonjour
j’espère que vous allez pouvoir m'aider
j'aimerais que mes deux code ci joint ne forme qu'un seul code, j'ai fait un autre code mais sa me renvoie la mm chose pour les 2 boucles differents

voici mes code:
  1. Sub NOMBRE_DE_SINISTRES_DECLARES()
  2.  
  3. Dim Date_Souscription_Adhésion As Range, Date_Survenance As Range
  4. Dim DernLigne As Long
  5. Dim nblignes(1 To 12, 2013 To 2017) As Long
  6. Dim i, j, k As Integer
  7. Dim a, b, c, d, e As Integer
  8.  
  9.  
  10. With Worksheets("Sinistre_Historique")
  11. DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
  12. Set Date_Souscription_Adhésion = .Range("G2:G" & DernLigne)
  13. Set Date_Survenance = .Range("U2:U" & DernLigne)
  14. End With
  15.  
  16. a = LBound(nblignes, 2)
  17. e = UBound(nblignes, 2)
  18.  
  19. For i = 2 To DernLigne
  20. If a <= Year(Cells(i, 21).Value) And Year(Cells(i, 21).Value) <= e Then
  21. j = Month(Cells(i, 7).Value)
  22. k = Year(Cells(i, 7).Value)
  23. nblignes(j, k) = nblignes(j, k) + 1
  24. End If
  25. Next i
  26.  
  27.  
  28. For i = 1 To 12
  29. For k = a To e
  30. Sheets("Feuil1").Cells(i + (k - 2013) * 12, 3).Value = nblignes(i, k)
  31. Next k
  32. Next i
  33.  
  34.  
  35. End Sub
  36. Sub NOMBRE_DE_SINISTRES_ACCEPTES()
  37.  
  38. Dim Date_Souscription_Adhésion As Range, Date_Survenance, Statut_Technique_Sinistre As Range
  39. Dim DernLigne As Long
  40. Dim nblignes(1 To 12, 2013 To 2017) As Long
  41. Dim i, j, k As Integer
  42. Dim a, b, c, d, e As Integer
  43. Dim s As String
  44.  
  45. With Worksheets("Sinistre_Historique")
  46. DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
  47. Set Date_Souscription_Adhésion = .Range("G2:G" & DernLigne)
  48. Set Date_Survenance = .Range("U2:U" & DernLigne)
  49. Set Statut_Technique_Sinistre = .Range("V2:V" & DernLigne)
  50. End With
  51.  
  52. a = LBound(nblignes, 2)
  53. e = UBound(nblignes, 2)
  54.  
  55. For i = 2 To DernLigne
  56. s = Cells(i, "V").Value
  57. If s = "Terminé - accepté" Then
  58. If a <= Year(Cells(i, 21).Value) And Year(Cells(i, 21).Value) <= e Then
  59. j = Month(Cells(i, 7).Value)
  60. k = Year(Cells(i, 7).Value)
  61. nblignes(j, k) = nblignes(j, k) + 1
  62. End If
  63. End If
  64. Next i
  65.  
  66.  
  67. For i = 1 To 12
  68. For k = a To e
  69. Sheets("Feuil1").Cells(i + (k - 2013) * 12, 4).Value = nblignes(i, k)
  70. Next k
  71. Next i
  72.  
  73.  
  74. End Sub



et voici le code que j'ai essayé de faire et qu'il me renvoie les même valeurs pour les deux colonne

  1. [code2=vb]
    1. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">Sub NOMBRE_DE_SINISTRES_DECLARES()
    2. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim Date_Souscription_Adhésion As Range, Date_Survenance As Range
    3. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim Statut_Technique_Sinistre As Range
    4. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim DernLigne As Long
    5. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim nblignes(1 To 12, 2013 To 2017) As Long
    6. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim i, j, k, l, m, n As Integer
    7. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim a, b, c, d, e As Integer
    8. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Dim s As String
    9. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> With Worksheets("Sinistre_Historique_ICICDDE (3)")
    10. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
    11. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Set Date_Souscription_Adhésion = .Range("G2:G" & DernLigne)
    12. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Set Date_Survenance = .Range("U2:U" & DernLigne)
    13. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Set Statut_Technique_Sinistre = .Range("V2:V" & DernLigne)
    14. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> End With
    15. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> a = LBound(nblignes, 2)
    16. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> e = UBound(nblignes, 2)
    17. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> For i = 2 To DernLigne
    18. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> If a <= Year(Cells(i, 21).Value) And Year(Cells(i, 21).Value) <= e Then
    19. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> j = Month(Cells(i, 7).Value)
    20. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> k = Year(Cells(i, 7).Value)
    21. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> nblignes(j, k) = nblignes(j, k) + 1
    22. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> End If
    23. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Next i
    24. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> For l = 2 To DernLigne
    25. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> s = Cells(l, "V").Value
    26. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> If s = "Terminé - accepté" Then
    27. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> If a <= Year(Cells(l, 21).Value) And Year(Cells(l, 21).Value) <= e Then
    28. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> m = Month(Cells(l, 7).Value)
    29. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> n = Year(Cells(l, 7).Value)
    30. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> nblignes(m, n) = nblignes(m, n) + 1
    31. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> End If
    32. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> End If
    33. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Next l
    34. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> For i = 1 To 12
    35. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> For k = a To e
    36. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> 'condition is si numero contrat fichier tdb = numero police fichier sinistre
    37. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> 'changer sheets et mettre la bonne feuille
    38. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> 'condition sur mois et année pour pouvoir effectuer les macros
    39. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Sheets("Feuil1").Cells(i + (k - 2013) * 12, 3).Value = nblignes(i, k)
    40. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Next k
    41. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Next i
    42. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> For l = 1 To 12
    43. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> For n = a To e
    44. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Sheets("Feuil1").Cells(l + (n - 2013) * 12, 4).Value = nblignes(l, n)
    45. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Next n
    46. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;"> Next l
    47. </div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">&nbsp;</div></li><li style="font-weight: normal;"><div style="margin:0; padding:0; background:none;">End Sub</div></li></ol></pre>
    [/code2]


    merciii pour votre aide

Autres pages sur : code seul

a b L Programmation
21 Avril 2017 14:54:36

Heuuu... remets le code, parce que là on a les balises HTML dans ton code, c'est imbuvable.
m
0
l
21 Avril 2017 14:58:48

ah oui désolé j'avais aps vu

voila
  1. Sub Rentabilite()
  2.  
  3. Dim Date_Souscription_Adhésion As Range, Date_Survenance As Range
  4. Dim Statut_Technique_Sinistre As Range
  5. Dim DernLigne As Long
  6. Dim nblignes(1 To 12, 2013 To 2017) As Long
  7. Dim i, j, k, l, m, n As Integer
  8. Dim a, b, c, d, e, aa, ee As Integer
  9. Dim s As String
  10.  
  11. With Worksheets("Sinistre_Historique_ICICDDE01_8")
  12. DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
  13. Set Date_Souscription_Adhésion = .Range("G2:G" & DernLigne)
  14. Set Date_Survenance = .Range("U2:U" & DernLigne)
  15. Set Statut_Technique_Sinistre = .Range("V2:V" & DernLigne)
  16.  
  17.  
  18. a = LBound(nblignes, 2)
  19. e = UBound(nblignes, 2)
  20.  
  21. For i = 2 To DernLigne
  22. If a <= Year(Cells(i, 21).Value) And Year(Cells(i, 21).Value) <= e Then
  23. j = Month(Cells(i, 7).Value)
  24. k = Year(Cells(i, 7).Value)
  25. nblignes(j, k) = nblignes(j, k) + 1
  26. End If
  27. Next i
  28.  
  29. For i = 1 To 12
  30. For k = a To e
  31. 'condition is si numero contrat fichier tdb = numero police fichier sinistre
  32. 'changer sheets et mettre la bonne feuille
  33. 'condition sur mois et année pour pouvoir effectuer les macros
  34. Sheets("Feuil1").Cells(i + (k - 2013) * 12, 3).Value = nblignes(i, k)
  35. Next k
  36. Next i
  37.  
  38.  
  39. For l = 2 To DernLigne
  40. s = Cells(l, "V").Value
  41. If s = "Terminé - accepté" Then
  42. If a <= Year(Cells(l, 21).Value) And Year(Cells(l, 21).Value) <= e Then
  43. m = Month(Cells(l, 7).Value)
  44. n = Year(Cells(l, 7).Value)
  45. nblignes(m, n) = nblignes(m, n) + 1
  46. End If
  47. End If
  48. Next l
  49.  
  50.  
  51. For l = 1 To 12
  52. For n = a To e
  53. Sheets("Feuil1").Cells(l + (n - 2013) * 12, 4).Value = nblignes(i, n)
  54. Next n
  55. Next i
  56. End With
  57.  
  58.  
  59. End Sub
m
0
l
a b L Programmation
21 Avril 2017 16:58:37

Difficile là... cela nécessite une analyse complète du pourquoi et du comment... donc rien à voir avec de la simple fusion de code;)
m
0
l