Excel Makros zum Einfügen von Zeilen und Formeln

Computer: Software & Programmierung Office Software
#1

Liebe/-r Experte/-in,
ich suche für eine ziemlich lange Excel Tabelle (12000 Zeilen) ein Makro, welches mir die Spalte A, in welcher Artikelnummern stehen, durchsucht und vor jede neue Artikelnummer 4 leere Zeilen einfügt.
Das zweite Makro soll dann in die oberste leere Zeile (also nach jedem Block welcher zu einer Artikelnummer gehört) einige Formeln und berechnungen zu diesem Block schreiben, jedoch sind die Blöcke nicht immer gleich lang, bestehen also aus einer unterschiedlichen Anzahl von Zeilen.
Bei den Formeln handelt es sich um Teilergebnisse (Summen) und weitere Berechnungen mit diesen.

Ich hoffe es gibt dazu eine Lösung. Benutze XP und Office 2007, habe jedoch keine Erfahrung mit Markos =(.

Vielen Dank für jede Anregung und Hilfe,
Sebastian

#2

Hallo Sebastian,
grundsätzlich geht das natürlich, allerdings kann ich ein solches Makro nur schreiben, wenn ich etwas mehr über deine Tabelle weiß.
Also, wie kann ich die Zeilen, die die Artikelnummer enthalten erkennen, d.h. wie unterscheiden Sie sich von den anderen Zellen.
Dann muss ich natürlich wissen, was für Formeln du einfügen möchtest.
Am besten, du teils mir deine EMail Adresse mit, dann können wir Dateien austauschen.

Gruß
Anne

#3

Hallo,

man kann dafür schon ein entsprechendes Makro schreiben. Allerdings müßte man dafür dann schon genauer wissen, wie die Daten aussehen und wie das Ergebnis aussehen soll.
Ich würde für so eine Problematik allerdings eine andere Lösung vorziehen. Ich würde mir eine Maske anlegen (so wie das Ergebnis aussehen soll) und diese dann aus dem Datenblatt mit verschachtelte SUMME(WENN… Funktionen befüllen.

#4

Lieber Sebastian
Dafür gibt es keine fertigen Macros, die musst Du Dir schon selbst entwickeln. Ich würde mich mit dem Macro Recorder vertraut machen, Macros Deiner Excelaktivitäten aufzeichnen und brauchbare Teile in Deinen neuen Macro kopieren. Hier noch ein Kommando dass Du dabei brauchen wirst:
ActiveCell.Offset(-1, 1).Select
(in diesem Fall: springe eine Zelle nach unten und eine Spalte nach rechts).

#5

Danke für die Antworten, aber da die Daten leider sehr vertraulich sind kann ist es mir nicht möglich genauere Daten herauszugeben.

Es handelt sich um Artikelnummern und Daten zu den Artikeln.
Beispielsweise so:
In Spalte A stehen die Artikelnummern.
in den Spalten danach stehen Verkaufszahlen.
Dann soll dass Makro die Zeilen in Spalte A durchsuchen und da dort zu einem Artikel mehrere Verkaufszahlen stehen sollen wenn ein neuer Artikel (neue / andere Artikelnummer) kommt soll zwischen den beiden Artikeln 4 leere Zeilen eingefügt werden.
Zum zweiten Teil soll in die Zeile direkt unter diesem Block (in dem die Verkaufszahlen eines Artikels stehen) die gesammt Verkaufszahl ausgerechnet werden.
Also eine Summe.

Mein bisheriger Versuch sieht so aus:
Dim x, i, j, ergo
x = Range(„A2“)
For i = 3 To 20000
ergo = Range(„A“ & i)
If ergo x Then
Worksheets(„Tabelle1“).Rows(i).Insert
x = ergo
End If
Next i

Da kommt jedoch auch immer nur eine Zeile dazu.

Ich hoffe die Infos reichen aus.
Vielen Dank
Sebastian

#6

Was ist jetzt genau die Frage ? Soll ich den Macro schreiben ?

#7

Die Sache mit den Zeilen habe ich inzwischen schon ertüftelt, meine Frage bezieht sich nun nur noch auf die Formeln.
Wie bekomme ich in jede erste freie Zeile nach einem Block z.b. die Formel für die Summe der Zahlen des Blocks darüber?

Bsp.:
A | 4
A | 3
A | 4
(freie Zeilen)

B | 2
B | 8
(freie Zeilen)

C | 3
C | 7
C | 2
C | 9
C | 2
(freie Zeilen)

Und in eben jene freie Zeile unter jedem Block soll die Summer der Zahlen stehn die diese Spalte in diesem Block enthält also unter

A | 4
A | 3
A | 4
| 11

B | 2
B | 8
| 10

C | 3
C | 7
C | 2
C | 9
C | 2
| 23

=> so
Und die Blöcke sind wie hier unterschiedlich lang, mal nur eine Zeile manchmal aber auch mehrere Zeilen.

Kann das Makro „aufwärts“ zusammenzählen bis zum nächsten freien Feld und die Summe dann unter den Block schreiben?

#8

In dem Du den Macro eine Summe bilden lässt nach diesem Muster:

ActiveCell.FormulaR1C1 = „=SUM(R[-14]C:R[-1]C)“

Dabei musst Du -14 (also 14 Rows nach oben in meinem Beispiel durch eine Variable ersetzen).
Dein Macro muss dazu beim Erzeugen der unterschiedlich langen Blöcke in diese Variable die Länge der Blöcke abspeichern. Dann kannst Du diese Variable in der Summenformel verwenden.

#9

Hallo Sebastian
Leider kann ich dir auch nicht helfen. Bin bis jetzt ohne Makro ausgekommen.
Viel Erfolg
Jürgen Stirnberg

#10

Hi Sebastian,

das Makro selbst glaube ich ist kein Problem, aber die Anforderung ist etwas unpräzise.
Brauchst du zwei Makros, oder kann das nicht gleich alles in einem gemacht werden? Gibt man die Artikelnummer an, die man sucht, oder sucht man mehrere aus einer Liste? Wenn vor jeder Arikelnummer 4 Leerzeilen eingefügt werden sollen, dann verstehe ich die unterschiedliche Größe der Blöcke nicht.

Ich bin mir nicht sicher wie ich Dir am besten helfen kann, da du meinst kein Makro Erfahrung zu haben.

Ich würde es demnach so einfach wie möglich halten.

Hier ein kleines Makro, dass die Spalte A von unten nach oben durchsucht. Der Einfachheit halber einfach mal von Zeile 1300 zu Zeile 2

Den Makro-Editor öffnen (Alt+F11) und links im Projektfester "Tabelle 1"öffnen oder „Diese Arbeitsmappe“ doppelklicken.
Dann im Fenster folgendes Maro einfügen.

Sub durchlaufeTabelleVonUntenNachOben()
 'Variablen als Ganzzahlen deklarieren
 Dim aktuelleZeile, StartZeile, StopZeile As Integer
 Dim BerechnungsoptionMerken As Variant

 'Damit es schneller geht die automatische Berechnung und Die Monitordarstellung deaktivieren
 BerechnungsoptionMerken = Application.Calculation
 Application.Calculation = xlCalculationManual 'ganz WICHTIG, SONST DAUERT DAS MAKRO EWIG
 Application.ScreenUpdating = False

 'Start und Stopzeile definieren
 StartZeile = 1300
 StopZeile = 2

 'Durchlaufe alle Zeilen von Startzeile bis Stopzeile
 For aktuelleZeile = StartZeile To StopZeile Step -1

 'Prüfe den Wert der aktuellen Ziele in Spalte 1 (=A), ist dieser
 'leer dann führe einige Befehle aus
 'Hier kann man auch nach anderen Kriterien suchen
 If ActiveSheet.Cells(aktuelleZeile, 1).Value "" Then
 '4 mal eine Zeile einfügen
 ActiveSheet.Rows(aktuelleZeile).Insert Shift:=xlDown
 ActiveSheet.Rows(aktuelleZeile).Insert Shift:=xlDown
 ActiveSheet.Rows(aktuelleZeile).Insert Shift:=xlDown
 ActiveSheet.Rows(aktuelleZeile).Insert Shift:=xlDown

 'In die erste Zeile in Spalte A eine Formel einfügen (Teilergebnis(3, aktuelle Zeile, Spalte A zu aktuelle Zeile +4)
 ActiveSheet.Cells(aktuelleZeile, 1).FormulaR1C1 = "=SUBTOTAL(3, RC:R[" & 4 & "]C)"

 'In Spalte B was reingeschrieben, um zu zeigen, dass sich was getan hat.
 ActiveSheet.Cells(aktuelleZeile, 2).Value = "Neue Formel in Spalte A"
 End If

 Application.StatusBar = "Zeile: " & aktuelleZeile
 Next

 GoTo FehlerfreiDurchgelaufen

Fehler:
 MsgBox "Es ist ein Fehler aufgetreten: " & Err.Description, vbCritical

FehlerfreiDurchgelaufen:
 Application.Calculation = BerechnungsoptionMerken
 Application.ScreenUpdating = True

End Sub

Ich bezweifle zwar, dass es das ist, was du brauchst aber vielleicht hilfes, am besten nimmst du mit dem Makro-Rekorder einige Aktionen auf und schaust Sie dir im Code an. Wenn du spezifischere Hilfe brauchst, bitte evtl. eine Beispieldatei senden oder besser spezifizieren.

Gruß,
Frank

#11

Vielen Dank für die Hilfe, da ist schon viel dabei was ich verwenden kann!!!

Es ist so, dass eine Tabelle vorhanden ist in der die Werte vorhanden sind, also beispielsweise Artikelnummern und zugehörige Verkaufszahlen pro Monat. Und da z.b. manche Produkte in manchen Monaten nicht verkauft wurden, werden diese garnicht erst aufgeschrieben. Also habe ich eine Liste von beispielsweise Artikelnummern und zum ersten Artikel 3 Verkaufszahlen, zum zweiten Produkt (also zweite Artikelnummer 9 Verkaufszahlen) zur dritten Artikelnummer vielleciht sogar nur einen Verkaufsmonat => eine Verkaufszahl. Und da bei 120000 Zeilen die Berechnungsformeln für jeden Block einzeln manuell einzugeben sehr zeitaufwändig ist und diese Makros auch in Zukunft zum Einsatz kommen sollen.

Genau spezifiziert soll das Makro in der Spalte G arbeiten und die einzelnen zeilen in G durchsuchen und (angenommen wir arbeiten von oben hinunter) sobald eine leere Zeile kommt (also ein Block zu einer Artikelnummer beendet ist) soll die Zahlen von dieser Zelle aus nach oben bis zur nächsten freien Zelle (also der Zeile VOR dem Beginn dieses Blocks) zusammenzählen und unter diesen Block in die Zelle der Spalte G schreiben.
Gleiches gilt für die Spalten H und I.
In Spalte J soll die Rechnung „Wert aus G“ aus der selben Zeile geteilt durch „Wert aus I“ aus dieser Zeile.
In Spalte K das gleiche für „Wert aus H“ / „Wert aus I“.
In Spalte L soll „Wert aus G“/„Wert aus H“ *100
In Spalte M der Wert aus J direkt übertragen.
Spalte N soll „Wert aus M“ * „Wert aus I“ enthalten,
Spalte O den originalwert aus Spalte H.
Schließlich und endlich Spalte P soll „Wert aus N“ durch „Wert aus O“ * 100.

Einiges zu tun =/
Hoffe du hast noch den ein oder anderen Tipp :wink:

Danke nochmal
Sebastian

#12

Hallo Sebastian,

so ganz hab ich das Problem noch nicht verstanden, dazu bräuchte ich noch ein paar Infos.
Kommt jede Artikelnummer in Spalte A nur einmal vor oder sollen die 4 Zeilen nur dann eingebaut werden, wenn die Nummer weiter oben noch nicht aufgetaucht ist?

Und warum genau 4 zeilen, wenn Du sagst, es muss eine unterschiedliche Anzahl Zeilen eingefügt werden?

Kannst Du vielleicht mal ein Beispiel geben, was Du genau erreichen willst? Unter Umständen kann man das auch etwas einfacher gestalten.

Gruß Olaf

#13

Hi Sebastian,

schonmal daran gedacht Microsoft Access zu verwenden?
Ich muss leider gestehen, dass ich Deine Spec nicht so richtig verstehe. Welche Werte stehen in Spalten und welche in Zeilen?
Evtl. kannst du ja mal einen kleinen Auszug (max. 100 Zeilen) der Tabelle schicken. Eine weitere Frage ist natürlich auch, was mit den Daten weiterhin gemacht werden soll. Im Moment scheint mir hier eine Datenbank (Access) das Mittel zur Wahl zu sein, da kannst du auch ganz einfach deine Excel Tabellen einfügen und ggf. neue, modizizierte Listen, Auswertungen, … erstellen.

Ich verstehe zum beispiel nicht, dass wir Spalte G auf leere Werte suchen und diese dann addieren sollen. Leere Zellen sind immer 0. Weiterhin sagst du, wenn in der durchsuchten Zeile in Spalte G ein Wert drin ist soll da die Formel rein, d.h. der Wert wird überschrieben?
Im Moment etwas crazy für mich.

Gruß,
Frank

#14

Hallo Sebastian,

ausgezeichnet, dass Du keine Erfahrung mit Makros hast -
brauchst nämlich dafür nicht:
ich mach so etwas immer mit „sortieren“:
eine Spalte einfügen, mit 1 beginnen, dann, wenn in der Artikelspalte nichts, oder die Artikelnr. steht:
=WENN(B5=$B$1;A5+1;A5+6)
im Prinzip: B= Artikelspalte, mit Leerfeld vergleichen, falls zutrifft, um eins weiter zählen, falls nicht zutrifft, also Artikelnr. drinnen steht, um sechs weiter zählen

dann ganz unten Leerzeilen machen, mit Zahlen in Spalte A von 1 bis 25000 …
Alles sortieren nach Spalte A, jetzt ist hinter jeder normalen Zeile eine Leerzeile, und hinter jeder Artikelzeile fünf Leerzeilen

nun kann man noch eine Spalte einfügen und
=WENN(B5=B4;B5+100000;B5)
B ist die alte Sortierspalte, wenn zwei Zahlen gleich sind, wird die zweite Zahl um 100000 erhöht - auf diese Weise werden die Leerzeilen, die nicht hinter den Artikelzeilen stehen, nach einer neuen Sortierung nach Spalte A nach unten verschoben

sorry, ich hoffe, es war halbwegs verständlich,
bei solchen Sachen ein bisschen Fantasie, meistens geht´s ohne Makro ganz elegant (auch wenn mein Weg jetzt nicht der eleganteste war, aber es ist in drei Minuten erledigt)

falls Du noch Hilfe brauchst, kann ich Dir das ganze auch in einem Beispiel- File schicken

gute Nacht,

Ralf

#15

Ich denke das läßt sich mit einem einfachem Makro nicht abdecken. Hierzu müsstest Du dich schon mit VBA beschäftigen.