Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
988to992
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
988to992
988to992
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Schleife statt Select, Makro optimieren

Schleife statt Select, Makro optimieren
29.06.2008 21:52:48
edie
Hallo zusammen,
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

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Schleife statt Select, Makro optimieren
29.06.2008 22:50:00
Tino
Hallo,
nicht getestet, versuche es mal hiermit.

Sub Makro1()
Dim ZE As Long
ZE = Cells(Rows.Count, 2).End(xlUp).Row
Range("L3:L26").FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R3C11)*R7C[-9]:R" & ZE & "C[-9])" & _
"/COUNTIF(R7C2:R" & ZE & "C2,""=""&R3C11)"
Range("L3:L26").AutoFill Destination:=Range("L3:S26"), Type:=xlFillDefault
End Sub


Gruß Tino

www.VBA-Excel.de


AW: Schleife statt Select, Makro optimieren
29.06.2008 23:09:00
edie
Hallo Tino,
vielen Dank für die Hilfe, leider funktioniert es noch nicht.
Im der Formel sollte der Bezug: C2=R3C11 relativ sein d.h.
in der Zelle L3 C2=R3C11, in der Zelle L4 C2=R4C11, in der Zelle L5 C2=R5C11
und so werter.
Wie könnte man auch das noch lösen? Damit in der R3C11 der Zeilenbezug relativ wäre.

Sub Makro11()
Dim ZE As Long
ZE = Cells(Rows.Count, 2).End(xlUp).Row
Range("L3:L26").FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=R3C11)*R7C[-9]:R" & ZE & "C[-9])" & _
"/COUNTIF(R7C2:R" & ZE & "C2,""=""&R3C11)"
Range("L3:L26").AutoFill Destination:=Range("L3:S26"), Type:=xlFillDefault
End Sub


Vielen herzlichen Dank im Voraus.
Grüße

Anzeige
AW: Schleife statt Select, Makro optimieren
29.06.2008 23:23:48
Tino
Hallo,
die R1C1 Bezüge sind mir auch zu kompliziert.
Ich behelfe mir immer so, nimm eine Zelle, schreibe in diese die korrekte Formel, also mit den richtigen Bezügen.
(würdest du die Zelle nach unten ziehen, müsste sich die Formel richtig anpassen).
Starte eine Makroaufzeichnung,
mach ein doppelklick in diese Zelle und drücke die Enter Taste,
Aufzeichnung stoppen.
Diese Formel ersetzt du mit der im Makro und schon sollte es funktionieren.
Gruß Tino

www.VBA-Excel.de


AW: Schleife statt Select, Makro optimieren
29.06.2008 23:33:17
edie
Hallo Tino,
Danke für den Tipp ich wede' s probieren.
Schönen Abend noch und vielen Dank.
Grüße

Anzeige
AW: Schleife statt Select, Makro optimieren
30.06.2008 00:00:55
Tino
Hallo,
bei Array Formeln geht meine Version scheinbar nicht so.
Versuche es mal hiermit

Sub Makro1()
Dim ZE As Long, A As Long
ZE = Cells(Rows.Count, 2).End(xlUp).Row
Range("L" & A + 3).FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=RC[-1])*R7C[-9]:R" & _
ZE & "C[-9])/COUNTIF(R7C2:R" & ZE _
& "C2,""=""&RC[-1])"
Range("L3").AutoFill Destination:=Range("L3:L26"), Type:=xlFillDefault
Range("L3:L26").AutoFill Destination:=Range("L3:S26"), Type:=xlFillDefault
End Sub


Gruß Tino

www.VBA-Excel.de


Anzeige
AW: Schleife statt Select, Makro optimieren
30.06.2008 00:11:45
edie
Hallo Tino,
ich habe es so gelöst:

Sub Makro3()
Dim ZE As Long
Range("L3").Select
ZE = Cells(Rows.Count, 2).End(xlUp).Row
Selection.FormulaArray = _
"=SUM((R7C2:R" & ZE & "C2=RC11)*R7C[-9]:R" & ZE & "C[-9])/COUNTIF(R7C2:R" & ZE & "C2,""= _
""&RC11)"
Selection.AutoFill Destination:=Range("L3:L26"), Type:=xlFillDefault
Range("L3:L26").Select
Selection.AutoFill Destination:=Range("L3:S26"), Type:=xlFillDefault
Range("L3:S26").Select
Range("L3").Select
End Sub


In deinem Makro funktioniert nur die Spalte L das kopieren in die Spalten
M bis S verursacht #DIV/0!
Aber sost es schon Okay.
Viele Grüße und Danke

Anzeige
AW: es funktioniert, Danke
30.06.2008 00:03:39
edie
Hallo Tino,
der Anzatz war richtig es funktioniert.
Danke und Grüße

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige