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
| Sub ENT_BET_Traitement()
Application.ScreenUpdating = False
Dim TabYes
Set Ent = Worksheets(fEnt)
Set Ret = Worksheets(fRetenue)
Set Amt = Worksheets(fAmt)
Set Spa = Worksheets(fSuiviPA)
Set Aor = Worksheets(fAor)
Ent.Activate
Range("F3").Select
ReDim TabYes(Application.WorksheetFunction.CountIf(Columns("F"), "Oui"), 6)
While Not IsEmpty(ActiveCell.Value)
Select Case ActiveCell.Value
Case "Oui"
n = n + 1
TabYes(n, 1) = Cells(ActiveCell.Row, 1)
TabYes(n, 2) = Cells(ActiveCell.Row, 2)
TabYes(n, 3) = Cells(ActiveCell.Row, 3)
TabYes(n, 4) = Cells(ActiveCell.Row, 4)
TabYes(n, 5) = Cells(ActiveCell.Row, 5)
TabYes(n, 6) = Cells(ActiveCell.Row, 7)
End Select
ActiveCell.Offset(1, 0).Select
Wend
Ret.Activate
Range("A1").End(xlDown).Offset(1, 0).Select
For n = 1 To UBound(TabYes, 1)
Select Case Application.WorksheetFunction.CountIf(Columns("A"), TabYes(n, 1))
Case Is = 0
ActiveCell.Value = TabYes(n, 1)
ActiveCell.Offset(0, 1).Value = TabYes(n, 2)
ActiveCell.Offset(0, 2).Value = TabYes(n, 3)
ActiveCell.Offset(0, 3).Value = TabYes(n, 6)
ActiveCell.Offset(1, 0).Select
End Select
Next n
Cells.Borders.LineStyle = xlNone
With Range("A1").CurrentRegion
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
Amt.Activate
Range("A1").End(xlDown).Offset(1, 0).Select
For n = 1 To UBound(TabYes, 1)
Select Case Application.WorksheetFunction.CountIf(Columns("A"), TabYes(n, 1))
Case Is = 0
ActiveCell.Value = TabYes(n, 1)
ActiveCell.Offset(0, 1).Value = TabYes(n, 2)
ActiveCell.Offset(0, 2).Value = TabYes(n, 3)
ActiveCell.Offset(1, 0).Select
End Select
Next n
Cells.Borders.LineStyle = xlNone
With Range("A1").CurrentRegion
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
End Sub |
Partager