Berechnung mittels Makro in variabler Bereichslänge

19/08/2008 - 20:22 von Bruno Sutter | Report spam
Hallo liebe Newsgroup Helfer
Ich habe folgende Ausgangslage:
In ein neues Tabellenblatt wird aus einer anderen Applikation mittels copy /
paste in die Spalten A und B Text und Zahlen kopiert. In Spalte A ist der
Text in Spalte B die Zahlen und zwar in 3er Blöcken. Die Lànge (Anzahl
Zeilen) ist variabel. Nun soll mittels Makro folgendes erreicht werden:

1. Die Spalte B soll im benutzten Bereich das Zahlenformat #'##0 haben
2. In der Spalte D sollen folgende Berechnungen gemacht werden: D3 =
B3-B2-B1 in D6 = B6-B5-B4 usw z.B. bis D99 = B99-B98-B97
3. Danach soll in der Zelle B101 (also 2 Zeilen unter der letzten Zeile) die
Summe gebildet werden aus B1, B4, B7, B10 etc. bis B96
4. In Zelle B102 die Summe aus B2, B5, B8, B11 bis B97
5. In Zelle B103 die Summe aus B3, B6, B9, B12 bis B99
6. In Zelle C 103 = B103-B102-B101
7. Und in D103 die Summe der Zahlen von D3:D99
8. Ausserdem soll in A101 = "Vortag", A102 = "Bewegungen" und in A103 =
"Aktuell" geschrieben werden.

Ist dies überaupt so einfach machbar, bei einem fixen Bereich könnten
mittels Makro fix die Formeln in die entsprechenden Zellen geschrieben
werden, aber bei einer variablen Bereichslànge müsste es wohl eher mit einem
Loop-Befehl funktionieren.
Vielen Dank jedenfalls für Eure geschàtzten Vorschlàge.
Freundliche Grüsse
Bruno Sutter
 

Lesen sie die antworten

#1 Reiner Wolff
19/08/2008 - 21:26 | Warnen spam
Moin Bruno,

*Bruno Sutter* schrieb:
In ein neues Tabellenblatt wird aus einer anderen Applikation mittels copy /
paste in die Spalten A und B Text und Zahlen kopiert. In Spalte A ist der
Text in Spalte B die Zahlen und zwar in 3er Blöcken. Die Lànge (Anzahl
Zeilen) ist variabel. Nun soll mittels Makro folgendes erreicht werden:



Vieles davon brauchst Du nicht über ein Makro zu lösen, sondern kannst dies
mit Tabellenblattfunktionen lösen.

1. Die Spalte B soll im benutzten Bereich das Zahlenformat #'##0 haben



Warum nur im benutzten Bereich?
Stelle die gesamte Spalte B direkt in der Vorlagendatei auf das Format und
Du brauchst nichts weiter tun.

2. In der Spalte D sollen folgende Berechnungen gemacht werden: D3 > B3-B2-B1 in D6 = B6-B5-B4 usw z.B. bis D99 = B99-B98-B97



Schreibe in D3 folgende Formel und kopiere sie soweit nach unten (per Hand
oder Makro) wie Du sie brauchst:
=WENN(REST(ZEILE();3)=0;B3-B2-B1;"")

3. Danach soll in der Zelle B101 (also 2 Zeilen unter der letzten Zeile) die
Summe gebildet werden aus B1, B4, B7, B10 etc. bis B96



Du meinst vermutlich bis B97?
Schreibe dazu in Zelle B101 die Formel:
=SUMMENPRODUKT((B1:B99)*(REST(ZEILE(B1:B99)+2;3)=0))

4. In Zelle B102 die Summe aus B2, B5, B8, B11 bis B97



Du meinst vermutlich bis B98?
Schreibe dazu in Zelle B102 die Formel:
=SUMMENPRODUKT((B1:B99)*(REST(ZEILE(B1:B99)+1;3)=0))

5. In Zelle B103 die Summe aus B3, B6, B9, B12 bis B99



Schreibe dazu in Zelle B101 die Formel:
=SUMMENPRODUKT((B1:B99)*(REST(ZEILE(B1:B99);3)=0))

6. In Zelle C 103 = B103-B102-B101



In VBA würde ich Dir dazu den .FormulaR1C1-Bezug empfehlen.

7. Und in D103 die Summe der Zahlen von D3:D99



Das ist dann lediglich noch eine Summenformel.
Das sollte machbar sein.

8. Ausserdem soll in A101 = "Vortag", A102 = "Bewegungen" und in A103 > "Aktuell" geschrieben werden.



Ok.

Ist dies überaupt so einfach machbar, ...



Naja, einfach ist immer relativ ;-)

... bei einem fixen Bereich könnten
mittels Makro fix die Formeln in die entsprechenden Zellen geschrieben
werden, aber bei einer variablen Bereichslànge müsste es wohl eher mit einem
Loop-Befehl funktionieren.



Mit einer Schleife (wie Loop) würde es auch gehen, ich würde Dir allerdings
empfehlen kopierbare Formeln zu benutzen.
Eigentlich gibt es nur zwei Schwierigkeiten:
- die letzte Zeile zu ermitteln und
- die Formeln im VBA richtig zusammenzusetzen

Die letzte Zeile kannst Du ungefàhr so ermitteln:
Dim lngLetzteZeile As Long
lngLetzteZeile = ActiveSheet.Cells(Rows.Count,2).end(xlUp).Row


Beim Formeln zusammensetzen könnte für Deinen Schritt 3 etwas herauskommen
wie:
ActiveSheet.Cells(lngLetzteZeile+2, 2).Formula = _
"=SUMPRODUCT((B1:B" & lngLetzteZeile & ")*(MOD(ROW(B1:B" & lngLetzteZeile & ")+2,3)=0))"


Den Rest liefert Dir eigentlich der MakroRekorder.
Schalte also einmal die Aufzeichnungsfunktion ein und passen den Code
entpsrechend an (oder schreibe das ganze direkt ohne Select und Selection).

Wenn Du dabei Fragen hast, poste einfach Deinen nicht funktionierenden Code
und Stelle Deine konkreten Fragen dazu.

Gruß aus Kiel
Reiner
"Who the fuck is General Failure? And why is he reading my harddisk?"
Ist das nicht der Kollege von "General Protection Fault" und "Major
Problem"?

Ähnliche fragen