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
| Sub RemettreABlanc_infos()
Feuil2.Cells(8, 3).Value = ""
Feuil2.Cells(2, 5).Value = ""
Feuil2.Cells(3, 5).Value = ""
Feuil2.Cells(4, 5).Value = ""
Feuil2.Cells(5, 5).Value = ""
Feuil2.Cells(5, 3).Value = ""
Feuil2.Cells(10, 5).Value = ""
Feuil2.Cells(10, 7).Value = ""
Feuil2.Cells(44, 7).Value = ""
Feuil2.Cells(7, 3).Value = ""
Feuil2.Cells(6, 3).Value = ""
Feuil2.Cells(1, 6).Value = ""
End Sub
Sub CreerUneFacture_infos()
' Creer une facture
' definir et initialiser les variables
Dim row, col, last_row, last_col, CodeClient As Integer
Dim CodesClient As String
row = 2
last_row = 2
col = 1
last_col = 12
CodesClient = ""
' determiner le nombre de lignes client (non vides, 1000 maxi)
Do While last_row < 10
If Feuil7.Cells(last_row, 2).Value = "" Then
CodesClient = Left(CodesClient, Len(CodesClient) - 2)
Exit Do
Else
CodesClient = CodesClient & Feuil7.Cells(last_row, 1).Value & ", "
last_row = last_row + 1
End If
Loop
' on initialise la Feuil2 (facultatif)
Feuil2.Cells(8, 3).Value = ""
Feuil2.Cells(2, 5).Value = ""
Feuil2.Cells(3, 5).Value = ""
Feuil2.Cells(4, 5).Value = ""
Feuil2.Cells(5, 5).Value = ""
Feuil2.Cells(5, 3).Value = ""
Feuil2.Cells(10, 5).Value = ""
Feuil2.Cells(10, 7).Value = ""
Feuil2.Cells(44, 7).Value = ""
Feuil2.Cells(7, 3).Value = ""
Feuil2.Cells(6, 3).Value = ""
Feuil2.Cells(1, 6).Value = ""
CodeClient = InputBox("Entrez le code client désiré : " & CodesClient)
' trouver la ligne client desiree
Do While row < last_row
If Feuil7.Cells(row, 1).Value = CodeClient Then
Exit Do
Else
row = row + 1
End If
Loop
'on rempli la Feuil2
Feuil2.Cells(8, 3).Value = Feuil7.Cells(row, 1).Value
Feuil2.Cells(2, 5).Value = Feuil7.Cells(row, 4).Value
Feuil2.Cells(3, 5).Value = Feuil7.Cells(row, 5).Value
Feuil2.Cells(4, 5).Value = Feuil7.Cells(row, 6).Value
Feuil2.Cells(5, 5).Value = Feuil7.Cells(row, 7).Value
Feuil2.Cells(5, 3).Value = Feuil7.Cells(row, 8).Value
Feuil2.Cells(10, 5).Value = Feuil7.Cells(row, 9).Value
Feuil2.Cells(10, 7).Value = Feuil7.Cells(row, 9).Value
Feuil2.Cells(44, 7).Value = Feuil7.Cells(row, 9).Value
Feuil2.Cells(7, 3).Value = Feuil7.Cells(row, 12).Value
Feuil2.Cells(6, 3).Value = Feuil7.Cells(row, 8).Value
Feuil2.Cells(1, 6).Value = Feuil7.Cells(row, 8).Value
' on imprime la Feuil2 ainsi faite
Feuil2.PrintOut Copies:=1
' on passe a la ligne suivante
last_row = last_row + 1
' Loops
End Sub
Sub ToutImprimer_infos()
' Imprimer toutes les Feuil2s
' definir et initialiser les variables
Dim row, col, last_row, last_col As Integer
row = 2
last_row = 1
col = 1
last_col = 12
' determiner le nombre de lignes client (non vides, 1000 maxi)
Do While last_row < 10
If Feuil7.Cells(last_row, 2).Value <> "" Then
Exit Do
Else
last_row = last_row + 1
End If
Loop
' cree une Feuil2 par ligne de Feuil7
Do While row < last_row
Feuil2.Cells(8, 3).Value = Feuil7.Cells(row, 1).Value
Feuil2.Cells(2, 5).Value = Feuil7.Cells(row, 4).Value
Feuil2.Cells(3, 5).Value = Feuil7.Cells(row, 5).Value
Feuil2.Cells(4, 5).Value = Feuil7.Cells(row, 6).Value
Feuil2.Cells(5, 5).Value = Feuil7.Cells(row, 7).Value
Feuil2.Cells(5, 3).Value = Feuil7.Cells(row, 8).Value
Feuil2.Cells(10, 5).Value = Feuil7.Cells(row, 9).Value
Feuil2.Cells(10, 7).Value = Feuil7.Cells(row, 9).Value
Feuil2.Cells(44, 7).Value = Feuil7.Cells(row, 9).Value
Feuil2.Cells(7, 3).Value = Feuil7.Cells(row, 12).Value
Feuil2.Cells(6, 3).Value = Feuil7.Cells(row, 8).Value
Feuil2.Cells(1, 6).Value = Feuil7.Cells(row, 8).Value
Feuil2.PrintOut Copies:=1
last_row = last_row + 1
Loop
End Sub |
