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
| Option Explicit
Sub MaVersionFinal()
Dim Res(), Abatt, TB_Source As Range, Vide As Byte, VA, Coll_PK As New Collection, K, Tab_Pk(), Nb_PK As Long, PK As String, i As Long, j As Long, X As Integer, Noms As String, L As Long, SOM As Double, TB_Desti As Range
Res = Application.Transpose(Array("Distance PK en Km", "PK", "Elève", "Nb élèves", "Elèves communs", "Total Km/élève", "Tarif", "Abattement", "Montant facturé", "Montant total/élève"))
Abatt = Array("10%", "23%", "37%")
On Error Resume Next
Set TB_Source = Application.InputBox(prompt:="Sélectionner une cellule", Title:="Choix du tableau source", Type:=8)
If TB_Source Is Nothing Then MsgBox "Sélection annulée": Exit Sub
With TB_Source.CurrentRegion
Vide = .SpecialCells(xlCellTypeBlanks).Count
If Vide >= 1 Then MsgBox "DONNÉES MANQUANTES" & vbCrLf & vbCrLf & "Vérifier votre tableau source !!!!!!!": Set TB_Source = Nothing: Exit Sub
VA = .Value
With .Columns.Item(2).Offset(1).Resize(.Rows.Count - 1, 2)
For i = 2 To .Rows.Count
If VA(i, 3) - VA(i, 2) <= 0 Then MsgBox "DONNÉES PK NON CONFORMES" & vbCrLf & vbCrLf & "Vérifier votre tableau source !!!!!!!": Exit Sub
Next
Nb_PK = .Cells.Count: ReDim Tab_Pk(1 To Nb_PK)
For i = 1 To Nb_PK: Tab_Pk(i) = Application.Small(.Value, i): Next
End With
For i = 2 To Nb_PK
If Tab_Pk(i - 1) <> Tab_Pk(i) Then
PK = Tab_Pk(i - 1) & " à " & Tab_Pk(i)
For j = 2 To .Rows.Count
If VA(j, 2) <= Tab_Pk(i - 1) And VA(j, 3) >= Tab_Pk(i) Then
Coll_PK.Add VA(j, 1), PK
If Err Then Err.Clear: If InStr(Coll_PK(PK), VA(j, 1)) = 0 Then Noms = Coll_PK(PK): Coll_PK.Remove PK: Coll_PK.Add Noms & " | " & VA(j, 1), PK
End If
Next
End If
Next
On Error GoTo 0
If TB_Source.Count = 1 And Not Application.Intersect(TB_Source, .Columns.Item(1)) Is Nothing Then
If Application.Match(TB_Source.Value, .Columns.Item(1), 0) > 1 Then X = Application.Match(TB_Source.Value, .Columns.Item(1), 0): j = X Else j = 2
Else
j = 2
End If
For j = j To IIf(X > 0, X, .Rows.Count)
i = 1
For Each K In Coll_PK
i = i + 1: If Tab_Pk(i) = Tab_Pk(i - 1) Then Do: i = i + 1: Loop While Tab_Pk(i) = Tab_Pk(i - 1)
If InStr(K, VA(j, 1)) > 0 Then
If InStr(K, VA(j, 1)) > 1 Then K = VA(j, 1) & " | " & Replace(K, " | " & VA(j, 1), "") Else Noms = K
ReDim Preserve Res(1 To 10, 1 To UBound(Res, 2) + 1): L = UBound(Res, 2)
Res(1, L) = Tab_Pk(i) - Tab_Pk(i - 1): Res(2, L) = Tab_Pk(i - 1) & " à " & Tab_Pk(i): Res(3, L) = VA(j, 1): Res(4, L) = Len(K) - Len(Replace(K, "|", "")) + 1: Res(5, L) = K
Res(7, L) = VA(j, 4): Res(8, L) = Abatt(IIf(Res(4, L) > 2, 2, Res(4, L) - 1)): Res(9, L) = Res(1, L) * VA(j, 4) * (1 - (Val(Res(8, L)) / 100)): SOM = SOM + Res(9, L)
End If
Next
Res(6, L) = VA(j, 3) - VA(j, 2): Res(10, L) = SOM: SOM = 0
Next
If MsgBox("Voulez vous choisir la destination du résultat ?", vbYesNo) = vbYes Then
Do
Set TB_Desti = Application.InputBox(prompt:="Sélectionner une cellule", Title:="CHOIX DE LA DESTINATION", Type:=8)
If TB_Desti.Count > 1 And Not TB_Desti Is Nothing Then MsgBox "Merci de ne sélectionner qu'une seule cellule"
Loop While TB_Desti.Count <> 1 Or TB_Desti Is Nothing
If TB_Desti Is Nothing Then Set TB_Desti = .Offset(, .Columns.Count + 3).Resize(1, 1)
Else
Set TB_Desti = .Offset(, .Columns.Count + 3).Resize(1, 1)
End If
Application.ScreenUpdating = False
With TB_Desti
.CurrentRegion.Clear
.Resize(UBound(Res, 2), UBound(Res)) = Application.Transpose(Res)
With .CurrentRegion
.HorizontalAlignment = xlCenter: .VerticalAlignment = xlCenter: .Columns.AutoFit
New_inter_Coul 2, .Cells, 3
.Borders.Value = 1
End With
End With
Application.ScreenUpdating = True
End With
Set TB_Source = Nothing: Set TB_Desti = Nothing
End Sub |
Partager