Option
Explicit
Sub
Mittelwert()
Dim
rngZielblattZelle
As
Excel.Range
Dim
rngBasisBereich
As
Excel.Range
Dim
rngBereich
As
Excel.Range
Dim
i
As
Long
With
tblZieldaten
Set
rngZielblattZelle = .Cells(.Rows.Count,
"A"
).
End
(xlUp)
If
Trim$(rngZielblattZelle.Value) <>
""
Then
Set
rngZielblattZelle = rngZielblattZelle.Offset(1)
End
With
With
tblBasisdaten
Set
rngBasisBereich = .Range(.Range(
"A1"
).
End
(xlDown), .Range(
"A1"
).
End
(xlToRight))
End
With
For
i = 1
To
WorksheetFunction.RoundUp(rngBasisBereich.Rows.Count / 6, 0)
Set
rngBereich = rngBasisBereich.Resize(6).Offset(6 * (i - 1))
Debug.Print rngBereich.Address(0, 0); Tab(12);
" | Mittelwert = "
& WorksheetFunction.AverageIf(rngBereich,
">0"
); Tab(50);
" -> "
& rngZielblattZelle.Address(0, 0, External:=
True
)
rngZielblattZelle.Value = WorksheetFunction.AverageIf(rngBereich,
">0"
)
Set
rngZielblattZelle = rngZielblattZelle.Offset(1)
Next
End
Sub