Schleife statt Select, Makro optimieren
29.06.2008 21:52:48
edie
den nachfolgenden Code habe ich per Recorder aufgezeichnet
und etwas angepasst.
In Abhängigkeit der Spaltelänge B (je Tag 24 Stunden am Tag
und das mehrere Tage in der Spalte A)werden in den Spalten
C bis J zur jeden Stunde ein Mittelwert berechnet,
z. B. der Mittelwert um 15:00, 16:00 usw. für mehrere Tage
Die Stunden 00:00 bis 23:00 stehen in der Spalte K3 bis K26
und die Ergebnisse in den Spalten L bis S endsprechend.
Nun meine Frage:
Wie könnte man ohne das wiederholende Offset und Select auskommen?
Wäre schön das Ganze in eine Schleife durchlaufen lassen.
In der Formel wird nur das C2=R3C11 angepasst. Siehe Code.
Sub Makro1()
Dim ZE As Long
Range("L3").Select
ZE = Cells(Rows.Count, 2).End(xlUp).Row
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R3C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R3C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R4C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R4C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R5C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R5C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R6C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R6C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R7C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R7C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R8C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R8C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R9C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,"" _
=""&R9C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R10C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R10C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R11C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R10C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R12C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R11C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R13C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R12C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R14C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R13C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R15C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R14C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R16C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R15C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R17C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R16C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R18C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R17C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R19C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R18C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R20C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R19C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R21C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R20C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R22C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R21C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R23C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R22C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R24C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R23C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R25C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R24C11)"
ActiveCell.Offset(1, 0).Select
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R26C11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2," _
"=""&R25C11)"
Range("L3:L26").Select
Selection.AutoFill Destination:=Range("L3:S26"), Type:=xlFillDefault
Range("L3:S26").Select
Range("L3").Select
End Sub
Kann mir jemand dabei helfe den Code optimieren?
Vielen Dank im Voraus.
Grüße