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
| Public Function trouvefincol(nlig As Integer, ncol As Integer) As Integer
While (Len(Trim(Cells(nlig, ncol))) <> 0)
nlig = nlig + 1
Wend
trouvefincol = nlig
End Function
Public Sub premiervide()
Dim nlig As Integer, ncol As Integer
ncol = 1
nlig = 5
ncol = 1
nlig = 5
i = trouvefincol(nlig, ncol)
sheet1.Cells(i, 1) = Feuil1.Cells(1, 3)
sheet1.Cells(i, 2) = Feuil1.Cells(6, 8)
sheet1.Cells(i, 3) = Feuil1.Cells(6, 9)
sheet1.Cells(i, 4) = Feuil1.Cells(6, 10)
sheet1.Cells(i, 5) = Feuil1.Cells(8, 8)
sheet1.Cells(i, 6) = Feuil1.Cells(8, 9)
sheet1.Cells(i, 7) = Feuil1.Cells(8, 10)
sheet1.Cells(i, 8) = Feuil1.Cells(10, 8)
sheet1.Cells(i, 9) = Feuil1.Cells(10, 9)
sheet1.Cells(i, 10) = Feuil1.Cells(10, 10)
sheet1.Cells(i, 11) = Feuil1.Cells(12, 8)
sheet1.Cells(i, 12) = Feuil1.Cells(12, 9)
sheet1.Cells(i, 13) = Feuil1.Cells(12, 10)
sheet1.Cells(i, 14) = Feuil1.Cells(14, 8)
sheet1.Cells(i, 15) = Feuil1.Cells(14, 9)
sheet1.Cells(i, 16) = Feuil1.Cells(14, 10)
sheet1.Cells(i, 17) = Feuil1.Cells(16, 8)
sheet1.Cells(i, 18) = Feuil1.Cells(16, 9)
sheet1.Cells(i, 19) = Feuil1.Cells(16, 10)
sheet1.Cells(i, 20) = Feuil1.Cells(18, 8)
sheet1.Cells(i, 21) = Feuil1.Cells(18, 9)
sheet1.Cells(i, 22) = Feuil1.Cells(18, 10)
Range(sheet1.Cells(5, 1), sheet1.Cells(i + 2, 22)).Borders.LineStyle = xlNone
Range(sheet1.Cells(5, 1), sheet1.Cells(i, 1)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 2), sheet1.Cells(i, 4)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 5), sheet1.Cells(i, 7)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 8), sheet1.Cells(i, 10)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 11), sheet1.Cells(i, 13)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 14), sheet1.Cells(i, 16)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 17), sheet1.Cells(i, 19)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(5, 20), sheet1.Cells(i, 22)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Cells(i + 2, 1).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 2), sheet1.Cells(i + 2, 4)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 5), sheet1.Cells(i + 2, 7)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 8), sheet1.Cells(i + 2, 10)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 11), sheet1.Cells(i + 2, 13)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 14), sheet1.Cells(i + 2, 16)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 17), sheet1.Cells(i + 2, 19)).BorderAround , ColorIndex:=1, Weight:=xlMedium
Range(sheet1.Cells(i + 2, 20), sheet1.Cells(i + 2, 22)).BorderAround , ColorIndex:=1, Weight:=xlMedium
sheet1.Cells(1, 5) = sheet1.Cells(i, 1)
Range(sheet1.Cells(i + 1, 1), sheet1.Cells(i + 1, 22)).ClearContents
sheet1.Cells(i + 2, 1) = "TOTAL"
Cells(i + 2, 2) = Application.WorksheetFunction.Sum(Range(Cells(5, 2), Cells(i, 2)))
End Sub |
Partager