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
| Sub Creation_Tableau()
Dim DerLigH As Long, DerLigA As Long, DerLigB As Long, DerCol As Long
Application.ScreenUpdating = False
'on efface l'ancien tableau de préparation et la colonne C (anciens résultats)
If I1 <> "" Then
DerLigH = [H2].End(xlDown).Row
DerCol = [H110000].End(xlToRight).Row
Range(Cells(2, "I"), Cells(DerLigH, DerCol)).Clear
End If
Columns(3).ClearContents
'on recrée le nouveau tableau de préparation
DerLigA = [A10000].End(xlUp).Row
DerLigB = [B10000].End(xlUp).Row
Range("A1:A" & DerLigA).Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("B1:B" & DerLigB).Copy
Range("I1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
'Ajout des formules
Range(Cells(2, "I"), Cells(DerLigA + 1, DerLigB + 8)).FormulaR1C1 = "=IF(LEFT(R1C,5)=LEFT(RC8,5),RC8&""_""&RIGHT(R1C,3),"""")"
Range(Cells(1, "H"), Cells(DerLigA + 1, DerLigB + 8)).Borders().Weight = xlThin
'on reconstitue la nouvelle liste
Lig = 1
For L = 2 To DerLigA + 1
For C = 9 To DerLigB + 8
If Cells(L, C) <> "" Then
Cells(Lig, "C") = Cells(L, C)
Lig = Lig + 1
End If
Next C, L
End Sub |
Partager