Bekijk de onderstaande video om te zien hoe je onze site als een web app op je startscherm installeert.
Opmerking: Deze functie is mogelijk niet beschikbaar in sommige browsers.
Sub Samenvatting(control As IRibbonControl)
'Samenvatting verlichting
Dim sv, a, b(10), i As Long, obj1 As Object, obj2 As Object, obj3 As Object, obj4 As Object, obj5 As Object, obj6 As Object, obj7 As Object, obj8 As Object, obj9 As Object, obj10 As Object
Dim wb As ThisWorkbook
Set obj1 = CreateObject("scripting.dictionary")
Set obj2 = CreateObject("scripting.dictionary")
Set obj3 = CreateObject("scripting.dictionary")
Set obj4 = CreateObject("scripting.dictionary")
Set obj5 = CreateObject("scripting.dictionary")
Set obj6 = CreateObject("scripting.dictionary")
Set obj7 = CreateObject("scripting.dictionary")
Set obj8 = CreateObject("scripting.dictionary")
Set obj9 = CreateObject("scripting.dictionary")
Set obj10 = CreateObject("scripting.dictionary")
Set wb = ThisWorkbook
With wb.Sheets("algemeen")
sv = .Range("L2:AL393")
For i = 1 To UBound(sv)
With Choose(sv(i, 1), obj1, obj2, obj3, obj4, obj5, obj6, obj7, obj8, obj9, obj10)
If sv(i, 4) <> "" Then
a = .Item(sv(i, 1) & sv(i, 4) & sv(i, 5) & sv(i, 7) & sv(i, 9))
If IsEmpty(a) Then a = b
a(0) = sv(i, 1)
a(1) = sv(i, 4)
a(2) = sv(i, 5)
a(3) = a(3) + IIf(sv(i, 6) = "", 0, sv(i, 6))
a(4) = a(4) + IIf(sv(i, 8) = "", 0, sv(i, 8))
a(5) = sv(i, 9)
.Item(sv(i, 1) & sv(i, 4) & sv(i, 5) & sv(i, 7) & sv(i, 9)) = a
End If
If sv(i, 13) <> "" Then
a = .Item(sv(i, 1) & sv(i, 13) & sv(i, 14) & sv(i, 16) & sv(i, 18))
If IsEmpty(a) Then a = b
a(0) = sv(i, 1)
a(1) = sv(i, 13)
a(2) = sv(i, 14)
a(3) = a(3) + IIf(sv(i, 15) = "", 0, sv(i, 15))
a(4) = a(4) + IIf(sv(i, 17) = "", 0, sv(i, 17))
a(5) = sv(i, 18)
.Item(sv(i, 1) & sv(i, 13) & sv(i, 14) & sv(i, 16) & sv(i, 18)) = a
End If
If sv(i, 22) <> "" Then
a = .Item(sv(i, 1) & sv(i, 22) & sv(i, 23) & sv(i, 25) & sv(i, 27))
If IsEmpty(a) Then a = b
a(0) = sv(i, 1)
a(1) = sv(i, 22)
a(2) = sv(i, 23)
a(3) = a(3) + IIf(sv(i, 24) = "", 0, sv(i, 24))
a(4) = a(4) + IIf(sv(i, 26) = "", 0, sv(i, 26))
a(5) = sv(i, 27)
.Item(sv(i, 1) & sv(i, 22) & sv(i, 23) & sv(i, 25) & sv(i, 27)) = a
End If
End With
Next i
aantal = Application.Max(obj1.Count, obj2.Count, obj3.Count)
If aantal > 0 Then
With Sheets("samenvatting")
.UsedRange.ClearContents
If obj1.Count > 0 Then
.Cells(2, 1).Resize(obj1.Count, 10) = Application.index(obj1.items, 0, 0)
.Cells(2, 1).Resize(obj1.Count, 10).Sort .Cells(2, 2), , .Cells(2, 3), , , , , 2
.Cells(2, 1).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 5).Address & ":" & .Cells(2 + obj1.Count, 5).Address & ")")
End If
If obj2.Count > 0 Then
.Cells(2, 8).Resize(obj2.Count, 10) = Application.index(obj2.items, 0, 0)
.Cells(2, 8).Resize(obj2.Count, 10).Sort .Cells(2, 8), , .Cells(2, 9), , , , , 2
.Cells(2, 8).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 12).Address & ":" & .Cells(2 + obj2.Count, 12).Address & ")")
End If
If obj3.Count > 0 Then
.Cells(2, 15).Resize(obj3.Count, 10) = Application.index(obj3.items, 0, 0)
.Cells(2, 15).Resize(obj3.Count, 10).Sort .Cells(2, 15), , .Cells(2, 16), , , , , 2
.Cells(2, 15).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 19).Address & ":" & .Cells(2 + obj3.Count, 19).Address & ")")
End If
If obj4.Count > 0 Then
.Cells(2, 22).Resize(obj4.Count, 10) = Application.index(obj4.items, 0, 0)
.Cells(2, 22).Resize(obj4.Count, 10).Sort .Cells(2, 22), , .Cells(2, 23), , , , , 2
.Cells(2, 22).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 26).Address & ":" & .Cells(2 + obj4.Count, 26).Address & ")")
End If
If obj5.Count > 0 Then
.Cells(2, 29).Resize(obj5.Count, 10) = Application.index(obj5.items, 0, 0)
.Cells(2, 29).Resize(obj5.Count, 10).Sort .Cells(2, 29), , .Cells(2, 30), , , , , 2
.Cells(2, 29).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 33).Address & ":" & .Cells(2 + obj5.Count, 33).Address & ")")
End If
If obj6.Count > 0 Then
.Cells(2, 36).Resize(obj6.Count, 10) = Application.index(obj6.items, 0, 0)
.Cells(2, 36).Resize(obj6.Count, 10).Sort .Cells(2, 36), , .Cells(2, 37), , , , , 2
.Cells(2, 36).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 40).Address & ":" & .Cells(2 + obj6.Count, 40).Address & ")")
End If
If obj7.Count > 0 Then
.Cells(2, 43).Resize(obj7.Count, 10) = Application.index(obj7.items, 0, 0)
.Cells(2, 43).Resize(obj7.Count, 10).Sort .Cells(2, 43), , .Cells(2, 44), , , , , 2
.Cells(2, 43).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 47).Address & ":" & .Cells(2 + obj7.Count, 47).Address & ")")
End If
If obj8.Count > 0 Then
.Cells(2, 50).Resize(obj8.Count, 10) = Application.index(obj8.items, 0, 0)
.Cells(2, 50).Resize(obj8.Count, 10).Sort .Cells(2, 50), , .Cells(2, 51), , , , , 2
.Cells(2, 50).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 54).Address & ":" & .Cells(2 + obj8.Count, 54).Address & ")")
End If
If obj9.Count > 0 Then
.Cells(2, 57).Resize(obj9.Count, 10) = Application.index(obj9.items, 0, 0)
.Cells(2, 57).Resize(obj9.Count, 10).Sort .Cells(2, 57), , .Cells(2, 58), , , , , 2
.Cells(2, 57).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 61).Address & ":" & .Cells(2 + obj9.Count, 61).Address & ")")
End If
If obj10.Count > 0 Then
.Cells(2, 64).Resize(obj10.Count, 10) = Application.index(obj10.items, 0, 0)
.Cells(2, 64).Resize(obj10.Count, 10).Sort .Cells(2, 64), , .Cells(2, 65), , , , , 2
.Cells(2, 64).Offset(aantal + 1, 4) = Evaluate("sum(" & .Cells(2, 68).Address & ":" & .Cells(2 + obj10.Count, 68).Address & ")")
End If
End With
End If
End With
Application.EnableEvents = True
End Sub
If sv(i, 13) <> "" Then
a = .Item(sv(i, 1) & sv(i, 13) & sv(i, 14))
If IsEmpty(a) Then a = b
a(0) = sv(i, 1)
a(1) = sv(i, 13)
a(2) = sv(i, 14)
a(3) = a(3) + IIf(sv(i, 15) = "", 0, sv(i, 15))
a(4) = a(4) + IIf(sv(i, 17) = "", 0, sv(i, 17))
.Item(sv(i, 1) & sv(i, 13) & sv(i, 14)) = a
End If
a(0) = sv(i, 1)
a(1) = sv(i, 13)
a(2) = sv(i, 14)
a(3) = a(3) + IIf(sv(i, 15) = "", 0, sv(i, 15))
a(4) = a(4) + IIf(sv(i, 17) = "", 0, sv(i, 17))
We gebruiken essentiële cookies om deze site te laten werken, en optionele cookies om de ervaring te verbeteren.