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
|
Sub GB_002()
Dim debut As Long
Dim f As Worksheet
Dim DernierL As Long
Dim LigneDest As Long
Dim i As Long
Dim a As Variant
Dim precedent As Variant
Set f = ActiveSheet
Set rg = f.Range("A:A").Find(what:="*", After:=f.[A1], LookIn:=xlValues, LookAt:=xlPart, SearchFormat:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If rg Is Nothing Then DernierL = 1 Else DernierL = rg.Row
f.Range("J:K").Clear
LigneDest = 2
debut = 0
For i = 2 To DernierL
a = f.Cells(i, "A").Value
If a = "" Then Stop
If Val(a) = debut Then
f.Cells(LigneDest, "J") = f.Cells(i, "A")
Call Verifie(precedent, f.Cells(LigneDest, "J"))
precedent = f.Cells(LigneDest, "J")
f.Cells(LigneDest, "K") = f.Cells(i, "B")
LigneDest = LigneDest + 1
ElseIf a > debut Then
While debut < a - 1
debut = debut + 1
f.Cells(LigneDest, "J") = debut
Call Verifie(precedent, f.Cells(LigneDest, "J"))
precedent = f.Cells(LigneDest, "J")
LigneDest = LigneDest + 1
Wend
f.Cells(LigneDest, "J") = f.Cells(i, "A")
Call Verifie(precedent, f.Cells(LigneDest, "J"))
precedent = f.Cells(LigneDest, "J")
debut = f.Cells(i, "A")
f.Cells(LigneDest, "K") = f.Cells(i, "B")
LigneDest = LigneDest + 1
Else
'If a < debut
f.Cells(i, "A").Select
MsgBox "erreur sur la ligne " & i
Stop
End If
Next
While debut < 6000
debut = debut + 1
f.Cells(LigneDest, "J") = debut
Call Verifie(precedent, f.Cells(LigneDest, "J"))
precedent = f.Cells(LigneDest, "J")
LigneDest = LigneDest + 1
Wend
f.Activate
f.Cells(LigneDest, "J").Select
End Sub
Sub Verifie(precedent, r As Range)
If Not IsEmpty(precedent) And Not (r = precedent Or r - precedent = 1) Then
r.Parent.Activate
r.Select
MsgBox "Erreur en " & r.Address
Stop
End If
End Sub |
Partager