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 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181
| Option Explicit
Dim L As Integer
Dim C As Integer
Dim L2 As Integer
Dim C2 As Integer
Dim PremL1 As Integer
Dim Ligne1 As Integer
Dim PremC1 As Integer
Dim Col1 As Integer
Dim PremL2 As Integer
Dim PremC2 As Integer
Dim Ligne2 As Integer
Dim Valeur
Dim LBloc As Integer
Dim P1 As String
Dim P2 As String
Dim P3 As String
Dim P4 As String
Dim P5 As String
Dim P6 As String
Dim P7 As String
Dim P8 As String
Dim P9 As String
Dim P10 As String
Dim P11 As String
Dim P12 As String
Dim P13 As String
Dim P14 As String
Dim P15 As String
Dim Sortie As Boolean
Dim DepBloc As Integer
Private Sub Initialisation()
LBloc = 4
P1 = "1ct": P2 = "Récapitulatif" 'Feuil1 lecture et Feuil2 ecriture
P3 = "1ct-2cts-5cts"
P4 = "5cts"
P5 = "5cts-10cts"
P6 = "10cts-20cts"
P7 = "20cts-50cts"
P8 = "50cts-1fr"
P9 = "1Fr"
P10 = "1Frs-2Frs5frs"
P11 = "5Frs"
P12 = "5Frs-10Frs"
P13 = "10Frs"
P14 = "10Frs (2)"
P15 = "10Frs (3)"
PremL1 = 1
PremC1 = 2
PremL2 = 2
PremC2 = 1
End Sub
Public Sub Copie_mise_en_forme_ligne()
'feuille 1ct
Call Initialisation
Sortie = False
L = PremL1: C = PremC1
While Not Sortie
If ThisWorkbook.Worksheets(P1).Cells(L, C) = "" And ThisWorkbook.Worksheets(P1).Cells(L + 1, C) = "" Then
Ligne1 = L - 1
Sortie = True
End If
L = L + 1
Wend
L = PremL1: Sortie = False
While Not Sortie
If ThisWorkbook.Worksheets(P1).Cells(L, C) = "" And ThisWorkbook.Worksheets(P1).Cells(L, C + 1) = "" Then
Col1 = C - 1
Sortie = True
End If
C = C + 1
Wend
L2 = PremL2: C2 = PremC2
L = PremL1: C = PremC1
DepBloc = PremL1
While L <= Ligne1 + 1
Valeur = ThisWorkbook.Worksheets(P1).Cells(L, C)
If Valeur <> "" Then
ThisWorkbook.Worksheets(P2).Cells(L2, C2) = Valeur
L = L + 1
C2 = C2 + 1
Else
If C >= Col1 Then
C = PremC1
L = L + 1
DepBloc = L
Else
L = DepBloc
C = C + 2
End If
L2 = L2 + 1
C2 = PremC2
End If
Wend
'feuille 1ct-2cts-5cts--------------------------------------------------------------------
While Not Sortie
If ThisWorkbook.Worksheets(P3).Cells(L, C) = "" And ThisWorkbook.Worksheets(P3).Cells(L + 1, C) = "" Then
Ligne1 = L - 1
Sortie = True
End If
L = L + 1
Wend
L = PremL1: Sortie = False
While Not Sortie
If ThisWorkbook.Worksheets(P3).Cells(L, C) = "" And ThisWorkbook.Worksheets(P3).Cells(L, C + 1) = "" Then
Col1 = C - 1
Sortie = True
End If
C = C + 1
Wend
' L2 = PremL2: C2 = PremC2
' L = PremL1: C = PremC1
' DepBloc = PremL1
While L <= Ligne1 + 1
Valeur = ThisWorkbook.Worksheets(P3).Cells(L, C)
If Valeur <> "" Then
ThisWorkbook.Worksheets(P2).Cells(L2, C2) = Valeur
L = L + 1
C2 = C2 + 1
Else
If C >= Col1 Then
C = PremC1
L = L + 1
DepBloc = L
Else
L = DepBloc
C = C + 2
End If
L2 = L2 + 1
C2 = PremC2
End If
Wend
'feuille 5cts--------------------------------------------------------------------------
While Not Sortie
If ThisWorkbook.Worksheets(P4).Cells(L, C) = "" And ThisWorkbook.Worksheets(P4).Cells(L, C) = "" Then
' Ligne1 = L
Sortie = True
End If
' L = L + 1
Wend
L = PremL1: Sortie = False
While Not Sortie
If ThisWorkbook.Worksheets(P4).Cells(L, C) = "" And ThisWorkbook.Worksheets(P4).Cells(L, C + 1) = "" Then
Col1 = C - 1
Sortie = True
End If
C = C + 1
Wend
' L2 = PremL2: C2 = PremC2
' L = PremL1: C = PremC1
' DepBloc = PremL1
While L <= Ligne1 + 1
Valeur = ThisWorkbook.Worksheets(P4).Cells(L, C)
If Valeur <> "" Then
ThisWorkbook.Worksheets(P2).Cells(L2, C2) = Valeur
L = L + 1
C2 = C2 + 1
Else
If C >= Col1 Then
C = PremC1
L = L + 1
DepBloc = L
Else
L = DepBloc
C = C + 2
End If
L2 = L2 + 1
C2 = PremC2
End If
Wend
End Sub |
Partager