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
| Sub codes()
Dim X, Y, nb_liga, nb_ligb, nb_ligc As Integer
'Nombre de lignes existantes dans la feuille Averfier
Worksheets("Averifier").Activate
Y = Application.WorksheetFunction.CountA(Range("A1:A" & Range("A65536").End(xlUp).Row))
'Nombre de lignes existantes dans la feuille p_export_codes
Worksheets("p_export_codes").Activate
X = Application.WorksheetFunction.CountA(Range("A1:A" & Range("A65536").End(xlUp).Row))
'code non renseigné
Worksheets("p_export_codes").Activate
For I = 2 To X
Y = Y + 1
If Worksheets("p_export_codes").Range("P" & CStr(I)).Value = "" Then
Select Case Worksheets("p_export_codes").Range("E" & CStr(I)).Value
Case 1 To 7, 13 To 16, 18, 19, 22, 30 To 38, 50, 55 To 57, 63, 65, 75, 78, 81, 150 To 153
Worksheets("p_export_codes").Range("P" & CStr(I)).Select
With Selection
.Interior.ColorIndex = 6
Worksheets("p_export_codes").Range("S" & CStr(I)).Value = "Code non renseigné"
Worksheets("p_export_codes").Range("P" & CStr(I)).Select
.EntireRow.Copy
Worksheets("Averifier").Range ("A" & CStr(Y))
End With
End Select
End If
Next I
End sub |
Partager