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 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
| Option Explicit
Dim a, b, d, e, f
Sub Tx_Bord()
Dim i As Long, J As Long, k As Long, NBd As Long, NCr As Long, DL As Long, LaDate As Long, x As Long, y As Long
Dim TypeCamp As String
Dim ShBd As Worksheet, ShTxB As Worksheet
Dim Plage As Range, C As Range, v As Range
Dim Prise(), Ouvrage(), Tronçon()
Dim tOuv As Object, tTrc As Object
Dim temp As Variant, temp1 As Variant, temp2 As Variant
Dim Ligne
Application.ScreenUpdating = False
Set ShBd = Worksheets("BD")
ShBd.AutoFilterMode = False
NBd = ShBd.Cells(ShBd.Rows.Count, 1).End(xlUp).Row
Set ShTxB = Worksheets("TxBord")
With ShTxB
DL = .Cells(.Rows.Count, 2).End(xlUp).Row
If DL > 7 Then .Range("A8:K" & DL).Clear
LaDate = .Range("C4") 'DATE
TypeCamp = .Range("H4") 'REFERENCE
With ShBd
'.AutoFilterMode = False
.Range("A1:AA" & NBd).AutoFilter Field:=18, Criteria1:=TypeCamp
.Range("A1:AA" & NBd).AutoFilter Field:=3, Criteria1:=CDate(LaDate)
Set tOuv = CreateObject("Scripting.Dictionary") 'définit le dictionnaire dico
For Each C In .Range("D2:D" & NBd).SpecialCells(xlCellTypeVisible)
tOuv(C.Value) = ""
Next C 'prochaine cellule de la boucle
temp = tOuv.keys 'récupère le dictionnaire sans doublon dans le tableau temp
For i = 0 To UBound(temp) 'boucle 1 : sur toutes les valeurs uniques du tableau temp
'Stop
.Range("A1:AA" & NBd).AutoFilter Field:=4, Criteria1:=temp(i)
Set tTrc = CreateObject("Scripting.Dictionary") 'définit le dictionnaire dico
Set Plage = .Range("E2:E" & NBd).SpecialCells(xlCellTypeVisible)
For Each C In Plage 'c In .Range("E2:E" & NBd).SpecialCells(xlCellTypeVisible)
If Not tTrc.Exists(C.Value) Then tTrc.Add C.Value, C.Offset(0, 1).Value
Next C 'prochaine cellule de la boucle
temp1 = tTrc.keys 'récupère le dictionnaire sans doublon dans le tableau temp
temp2 = tTrc.items
For J = 0 To UBound(temp1) 'boucle 1 : sur toutes les valeurs uniques du tableau temp
.Range("A1:AA" & NBd).AutoFilter Field:=5, Criteria1:=temp1(J)
'Stop
ShTxB.Cells(J + 8, 2) = temp(i) 'VAL4
ShTxB.Cells(J + 8, 3) = temp1(J) 'VAL5
ShTxB.Cells(J + 8, 4) = temp2(J) 'VAL6
ShTxB.Cells(J + 8, 5) = WorksheetFunction.Subtotal(101, ShBd.Range("I1:I" & NBd)) 'MOY VAL9
ShTxB.Cells(J + 8, 5).NumberFormat = "0"
ShTxB.Cells(J + 8, 6) = WorksheetFunction.Subtotal(101, ShBd.Range("J1:J" & NBd)) 'MOY VAL10
ShTxB.Cells(J + 8, 6).NumberFormat = "0"
' nouveau filtre pour somme1-somme2
.Range("A1:AA" & NBd).AutoFilter Field:=8, Criteria1:="=CPS", _
Operator:=xlOr, Criteria2:="=S"
a = WorksheetFunction.Subtotal(109, ShBd.Range("O1:O" & NBd))
.Range("A1:AA" & NBd).AutoFilter Field:=8, Criteria1:="=I", _
Operator:=xlOr, Criteria2:="=JI"
.Range("A1:AA" & NBd).AutoFilter Field:=11, Criteria1:="="
b = WorksheetFunction.Subtotal(109, ShBd.Range("O1:O" & NBd))
ShTxB.Cells(J + 8, 7) = a - b
.Range("A1:AA" & NBd).AutoFilter Field:=11
.Range("A1:AA" & NBd).AutoFilter Field:=8
ShTxB.Cells(J + 8, 9) = WorksheetFunction.Subtotal(104, ShBd.Range("G1:G" & NBd)) _
- WorksheetFunction.Subtotal(105, ShBd.Range("G1:G" & NBd)) 'max-min filtrer
d = ShTxB.Cells(J + 8, 9).Value
ShTxB.Cells(J + 8, 8) = (a - b) / (WorksheetFunction.Pi() * _
(WorksheetFunction.Convert(temp2(J), "in", "m") * d)) 'densité
ShTxB.Cells(J + 8, 8).NumberFormat = "0.00"" µA/m²"""
e = WorksheetFunction.Subtotal(103, ShBd.Range("I1:I" & NBd)) 'nbval filter
.Range("A1:AA" & NBd).AutoFilter Field:=9, Criteria1:="<=-600"
f = WorksheetFunction.Subtotal(103, ShBd.Range("I1:I" & NBd))
.Range("A1:AA" & NBd).AutoFilter Field:=9
ShTxB.Cells(J + 8, 10) = (f / e)
ShTxB.Cells(J + 8, 10).NumberFormat = "0%"
ShTxB.Cells(J + 8, 11) = "" '
Next J
Next i
.AutoFilterMode = False
End With
End With
Set ShBd = Nothing
Set ShTxB = Nothing
End Sub |
Partager