Excel-Makro: Zwischen- und Gesamt-Summe einfügen

Computer: Software & Programmierung Office Software
1

Hallo Leute!

Meine Tabelle sieht so aus, sie ist nach Datum sortiert:

-------------------------------------------------------
Name Nr. Datum Betrag1 Betrag2 Betrag3 Ergebnis
-------------------------------------------------------
Müller 10 10.04.2001 284,20 123,10 100,60 90,00 
Müller 13 22.07.2001 234,56 111,22 88,20 50,00
Müller 17 12.12.2001 322,11 159,11 119,99 100,00 
Müller 21 31.03.2002 288,99 134,56 111,11 100,00
Müller 25 15.08.2002 299,88 140,00 114,00 110,00
Müller 29 30.11.2002 246,80 120,20 95,95 60,00
Müller 32 02.02.2003 250,55 121,33 98,00 70,00
...

Und automatisch per Makro soll das daraus werden:

-------------------------------------------------------
Name Nr. Datum Betrag1 Betrag2 Betrag3 Ergebnis
-------------------------------------------------------
Müller 10 10.04.2001 284,20 123,10 100,60 90,00 
Müller 13 22.07.2001 234,56 111,22 88,20 50,00
Müller 17 12.12.2001 322,11 159,11 119,99 100,00
-------------------------------------------------------
 Summe: 240,00
-------------------------------------------------------
Müller 21 31.03.2002 288,99 134,56 111,11 100,00
Müller 25 15.08.2002 299,88 140,00 114,00 110,00
Müller 29 30.11.2002 246,80 120,20 95,95 60,00
-------------------------------------------------------
 Summe: 270,00
-------------------------------------------------------
Müller 32 02.02.2003 250,55 121,33 98,00 70,00
-------------------------------------------------------
 Summe: 70,00
-------------------------------------------------------
...
-------------------------------------------------------
 Gesamt: 590,00
=======================================================

Ich muß das Makro dazu bringen

  • zu erkennen wenn das Jahr (Spalte C) wechselt
  • an dieser Stelle eine Leerzeile (mit Rahmen oben und unten) einzufügen
  • den Text „Summe:“ in Spalte F und die Formel für eben diese in Spalte G einzufügen
  • das Ende der Tabelle zu finden
  • und dann dort die Gesamtsumme zu berechnen und einzufügen (mit Rahmen oben und doppelt unten)
  • … und sicher habe ich noch was übersehen

Herrje, und ich scheitere schon dabei, mit SUMMEWENN() auf das Jahr aus dem Datum zuzugreifen. :frowning:

=SUMMEWENN(C$4:C$21;"Berechnet das zwar schön, addiert aber natürlich alle vorhergehenden Jahre auch mit.


    =SUMMEWENN(C$4:C$21;UND("31.12.2000");G$4:G$21 

Liefert mir als Ergebnis immer 0.


    =SUMMEWENN(C$4:C$21;JAHR(C)="2001";G$4:G$21)

Liefert mir als Ergebnis immer 0.


    =SUMMEWENN(C$4:C$21;JAHR(C$4:C$21)="2001";G$4:G$21)

„Fehler in Formel“

Ich komm einfach nicht zurecht damit. \*grummel\*

Also ich würde mir sehr freuen, wenn mir jemand bei diesem für mich leider zu komplexen Problem helfen würde... ich kenne Makros, wie bereits gesagt, leider nur vom Aufzeichnen.

In der Hoffnung auf Eure Tips!

Gruß
Hans
2

SummeWenn …

=SUMMEWENN(C$4:C$21;"=SUMMEWENN(C$4:C$21;"Das mit dem Makro ist übrigens im Prinzip kein größeres Problem, aber aufwendig genug, um mich nicht damit beschäftigen zu können (keine Zeit).

Kristian

3

Hi Hans,
mein XL2000 kennt keine doppelte Strichlinie deshalb musste ich mit der Zeilenhöhe tricksen. Zeichne mal ein Makro auf indem du eine Zelle mit doppelter Strichline umrahmst und sage was dort bei Linestyle steht, dann kann man das einbauen.

Option Explicit
Sub tt()
Dim n, ws1, ws2, zei1, zei2, von, Summe
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")
ws2.Cells.RowHeight = 12.75
With ws2.UsedRange
 For n = 7 To 10
 .Borders(n).LineStyle = xlNone
 Next n
End With
With ws2
 .UsedRange.ClearContents
 For n = 8 To 9
 With .Range("A1:G1").Borders(n)
 .LineStyle = xlDash
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 Next n
 ws1.Range("A1:G1").Copy Destination:=.Range("A1")
 For n = 7 To 10 Step 3
 With .Range("A1:G1").Borders(n)
 .LineStyle = xlNone
 End With
 Next n

End With
With ws1
 .Rows(2).Copy Destination:=ws2.Rows(2)
 von = 2
 zei1 = 2
 zei2 = 2
 While .Cells(zei1, 1) ""
 zei1 = zei1 + 1
 zei2 = zei2 + 1
 If Year(Cells(zei1, 3)) = Year(Cells(zei1 - 1, 3)) Then
 .Rows(zei1).Copy Destination:=ws2.Rows(zei2)
 Else
 ws2.Cells(zei2, 6) = "Summe"
 ws2.Cells(zei2, 7) = Application.WorksheetFunction.Sum(ws2.Range(ws2.Cells(von, 7), ws2.Cells(zei2 - 1, 7)))
 Summe = Summe + ws2.Cells(zei2, 7)
 For n = 8 To 9
 With ws2.Range(ws2.Cells(zei2, 1), ws2.Cells(zei2, 7)).Borders(n)
 .LineStyle = xlDash
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 Next n
 zei2 = zei2 + 1
 von = zei2
 .Rows(zei1).Copy Destination:=ws2.Rows(zei2)
 End If
 Wend
 ws2.Cells(zei2 + 1, 6) = "Gesamtsumme"
 ws2.Cells(zei2 + 1, 7) = Summe
 For n = 8 To 9
 With ws2.Range(ws2.Cells(zei2 + 1, 1), ws2.Cells(zei2 + 1, 7)).Borders(n)
 .LineStyle = xlDash
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 Next n
 With ws2.Range(ws2.Cells(zei2 + 3, 1), ws2.Cells(zei2 + 3, 7)).Borders(8)
 .LineStyle = xlDash
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
 End With
 ws2.Rows(zei2 + 2).RowHeight = 3
End With
End Sub

Gruß
Reinhard

4

Hallo Leute!

Meine Tabelle sieht so aus, sie ist nach Datum sortiert:

> -------------------------------------------------------  
> Name Nr. Datum Betrag1 Betrag2 Betrag3 Ergebnis  
> -------------------------------------------------------  
> Müller 10 10.04.2001 284,20 123,10 100,60 90,00  
> Müller 13 22.07.2001 234,56 111,22 88,20 50,00  
> Müller 17 12.12.2001 322,11 159,11 119,99 100,00  
> Müller 21 31.03.2002 288,99 134,56 111,11 100,00  
> Müller 25 15.08.2002 299,88 140,00 114,00 110,00  
> Müller 29 30.11.2002 246,80 120,20 95,95 60,00  
> Müller 32 02.02.2003 250,55 121,33 98,00 70,00  
> ...

Ich muß das Makro dazu bringen

  • zu erkennen wenn das Jahr (Spalte C) wechselt
  • an dieser Stelle eine Leerzeile (mit Rahmen oben und unten)
    einzufügen
  • den Text „Summe:“ in Spalte F und die Formel für eben diese
    in Spalte G einzufügen
  • das Ende der Tabelle zu finden
  • und dann dort die Gesamtsumme zu berechnen und einzufügen
    (mit Rahmen oben und doppelt unten)
  • … und sicher habe ich noch was übersehen

Also ich würde mir sehr freuen, wenn mir jemand bei diesem für
mich leider zu komplexen Problem helfen würde… ich kenne
Makros, wie bereits gesagt, leider nur vom Aufzeichnen.

In der Hoffnung auf Eure Tips!

Gruß
Hans

Hallo Hans,

habe dir mal ein Makro gestrickt, dass die Zeilen für Summe: und Gesamt: einfügt und formatiert.

Sub Makro1()
'
 Dim wks1 As Worksheet

 Set wks1 = ActiveWorkbook.ActiveSheet
 Zeile1 = 2 'startzeile des Makros = 1. Zeile mit Datum
 Zeile = Zeile1
 With wks1
 ' Schleife bis in Spalte "C" Datumszelle leer ist
 Do Until IsEmpty(wks1.Cells(Zeile, "C"))
 Startzeile = Zeile
 Do Until Year(.Cells(Zeile, "C")) Year(.Cells(Zeile + 1, "C"))
 Zeile = Zeile + 1
 Loop
 Endzeile = Zeile
 Zeile = Zeile + 1
 .Cells(Zeile, "C").EntireRow.Insert
 'Summenzeile formatieren
 With .Range(.Cells(Zeile, "A"), .Cells(Zeile, "G"))
 .Borders(xlDiagonalDown).LineStyle = xlNone
 .Borders(xlDiagonalUp).LineStyle = xlNone
 .Borders(xlEdgeLeft).LineStyle = xlNone
 With .Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With .Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 .Borders(xlEdgeRight).LineStyle = xlNone
 .Borders(xlInsideVertical).LineStyle = xlNone
 End With
 'Summenzeile ausfüllen
 .Cells(Zeile, "F").Value = "Summe:"
 .Cells(Zeile, "G").FormulaR1C1 = "=SUM(R[" & Startzeile - Endzeile - 1 & "]C:R[-1]C)"
 Zeile = Zeile + 1
 Loop
 'Gesamtzeile formatieren
 With .Range(.Cells(Zeile, "A"), .Cells(Zeile, "G"))
 .Borders(xlDiagonalDown).LineStyle = xlNone
 .Borders(xlDiagonalUp).LineStyle = xlNone
 .Borders(xlEdgeLeft).LineStyle = xlNone
 With .Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = xlAutomatic
 End With
 With .Borders(xlEdgeBottom)
 .LineStyle = xlDouble
 .Weight = xlThick
 .ColorIndex = xlAutomatic
 End With
 .Borders(xlEdgeRight).LineStyle = xlNone
 .Borders(xlInsideVertical).LineStyle = xlNone
 End With
 'Gesamtzeile ausfüllen
 .Cells(Zeile, "F").Value = "Gesamt:"
 .Cells(Zeile, "G").FormulaR1C1 = \_
 "=SUMIF(R[" & Zeile1 - Zeile & "]C[-1]:R[-1]C[-1],""Summe:"",R[" & Zeile1 - Zeile & "]C:R[-1]C)"
 End With
End Sub

Gruß
Franz

5

*****
Hallo Reinhard und Franz!

Ganz herzlich Dank für die Mühe, die Ihr Euch gegeben habt! Ihr habt mir damit sehr geholfen, wenn ich auch zugeben muß, daß ich nicht alles richtig verstanden habe.
Damit keiner enttäuscht ist, verrate ich lieber nicht, welche Lösung ich nehme. :wink:

Super, damit ist das komplette Makro fertig (Blatt verschieben und umbennen hat auch dazu gehört).

Nur an 2 Rand-Problemen hänge ich jetzt noch fest:

1. Wie starte ich das Makro über einen Button?
Wenn ich einfach den Code über das Button-Kontextmenü ‚Code anzeigen‘ reinkopiere, läuft das Makro nicht durch.
Fehlermeldung: „Laufzeitfehler 1004: Die Select-Methode konnte auf das Range-Objekt nicht angewendet werden.“
Aber dort steht ja auch „Private Sub CommandButtonClick1()“, ist das die falsche Stelle? Oder kann man per Button nicht über mehrere Tabellenblätter arbeiten?

Per Symbol in einer benutzerdefinierten Symbolleiste klappt das nämlich wunderbar.
Nur erscheint diese Symbolleiste dann bei jedem Excel-Start.
Aber es soll sie nur kommen, wenn ich diese eine bestimmte Datei starte, deswegen wäre mir ein Button direkt in dieser Datei lieber.

2. Wie kann ich dem Benutzer eine Abfrage stellen?
Ich möchte sicherstellen, daß man nicht vergißt, vor (bzw. bei) Anlauf des Makros den richtigen Abnehmer auszuwählen.
Also eine Abfrage ala

---------------------------------------
"Ist der Abnehmer korrekt ausgewählt?"
 [Ja] [Nein]
---------------------------------------

Bei [Ja] soll das Makro weiterlaufen.
Bei [Nein] soll es direkt beendet werden, um den richtigen Abnehmer auszuwählen und dann das Makro nochmal zu starten.

So, also ich hoffe, ich gehe Euch nicht auf die Nerven und Ihr helft mir noch das letzte Stückchen weiter… :smile:

Gruß
Hans

6

Hallo Kristian!

Danke auch für Deine Antwort.
Aber die Datums-Werte sind schon „echt“.
Ist ja eigentlich auch logisch, daß es so:

=SUMMEWENN(C$4:C$21;"=SUMMEWENN(C$4:C$21;"Das

mit dem Makro ist übrigens im Prinzip kein größeres Problem,
aber aufwendig genug, um mich nicht damit beschäftigen zu
können (keine Zeit).

Kristian

7

Hallo Hans

Du kannst mit dem folgenden Code eine Symbolleiste für Deine spezielle Datei erstellen. Diese wird automatisch eingeblendet, sobald Du die Datei öffnest. Sie wird ausgeblendet, wenn Du zu einem anderen Excel-Fenster umschaltest. Sie wird gelöscht, wenn Du Deine spezielle Datei schließt.

  • Gehe bitte in den Visual Basic-Editor.

  • Klicke im Menü „Einfügen“ auf „Modul“.

  • Kopiere den folgenden Code in das Code-Fenster des Moduls:

    Public tbrAbnehmer As CommandBar
    Public mnuAbnehmer As CommandBarButton

    Sub Toolbar()
    On Error GoTo Fehler
    Set tbrAbnehmer = CommandBars.Add(Name:=„Abnehmer“, Position:=msoBarFloating, Temporary:=True)
    With tbrAbnehmer
    Set mnuAbnehmer = .Controls.Add(Type:=msoControlButton, ID:=1851)
    mnuAbnehmer.Style = msoButtonCaption
    mnuAbnehmer.Caption = „Abne&hmer“
    mnuAbnehmer.OnAction = „Abnehmer“
    End With
    Fehler:
    CommandBars(„Abnehmer“).Visible = True
    End Sub

    Sub DisableBar()
    On Error Resume Next
    CommandBars(„Abnehmer“).Enabled = False
    End Sub

    Sub EnableBar()
    On Error Resume Next
    CommandBars(„Abnehmer“).Enabled = True
    End Sub

    Sub Abnehmer()
    Dim Antwort As VbMsgBoxResult
    Antwort = MsgBox(„Sie haben den Button ‚Abnehmer‘ geklickt.“ & vbCr & _
    „Soll der weitere Code ausgeführt werden?“, vbYesNo + vbQuestion)
    If Antwort = vbNo Then Exit Sub
    MsgBox „Der Code, der an dieser Stelle ausgeführt werden sollte,“ & vbCr & _
    „steht in einer Prozedur, dessen Name mir unbekannt ist.“ & vbCr & _
    „Bitte ersetzen Sie den Code für diese Meldung durch den Code,“ & vbCr & _
    „den Sie eigentlich ausführen wollen.“
    End Sub

Doppelklicke nun im Projekt-Explorer auf „DieseArbeitsmappe“ und füge folgenden Code dort ein :

Private Sub Workbook\_Activate()
 EnableBar
End Sub
 
Private Sub Workbook\_Deactivate()
 DisableBar
End Sub
 
Private Sub Workbook\_Open()
 Toolbar
End Sub

Es handelt sich um eine Symbolleiste vom Typ ‚Floating‘. Wenn Du sie lieber oben bei den anderen anordnen möchtest, dann ersetze „msoBarFloating“ durch „msoBarTop“. Der Button „Abnehmer“ ruft das der „OnAction“-Eigenschaft zugewiesene Makro auf. Wenn Du sämtlichen Code eingefügt hast, klicke im Menü „Debuggen“ auf Kompilieren, beende den Visual Basic-Editor, speichere die Datei, beende Excel und öffne erneut die Datei.

Viele Grüße
Carsten

1 „Gefällt mir“
8

Hi Hans,

Ganz herzlich Dank für die Mühe, die Ihr Euch gegeben
habt! Ihr habt mir damit sehr geholfen, wenn ich auch
zugeben muß, daß ich nicht alles richtig verstanden habe.
Damit keiner enttäuscht ist, verrate ich lieber nicht, welche
Lösung ich nehme. :wink:

welche du genommen hast ist mir egal :smile: Wichtiger für mich und Franz (mal unterstelle) eine Rückmeldung ob es klappt oder auch nicht und welcher Fehler auftritt, nur so können wir uns verbessern.

1. Wie starte ich das Makro über einen Button?
Wenn ich einfach den Code über das Button-Kontextmenü ‚Code
anzeigen‘ reinkopiere, läuft das Makro nicht durch.
Fehlermeldung: „Laufzeitfehler 1004: Die Select-Methode konnte
auf das Range-Objekt nicht angewendet werden.“
Aber dort steht ja auch „Private Sub
CommandButtonClick1()“, ist das die falsche Stelle? Oder kann
man per Button nicht über mehrere Tabellenblätter arbeiten?

Der Button ist mir jetzt neu, setze mal in Egenschaften (Rechtsklick—Eigenschaften) TakeFocusOnClick auf False. May be es hilft.

2. Wie kann ich dem Benutzer eine Abfrage stellen?
Ich möchte sicherstellen, daß man nicht vergißt, vor (bzw.
bei) Anlauf des Makros den richtigen Abnehmer auszuwählen.
Also eine Abfrage ala

„Ist der Abnehmer korrekt ausgewählt?“
[Ja] [Nein]

Bei [Ja] soll das Makro weiterlaufen.
Bei [Nein] soll es direkt beendet werden, um den richtigen
Abnehmer auszuwählen und dann das Makro nochmal zu starten.

Sorry, welchen Abnehmer meinst du? Ich brauch manchmal Extrabelehrung bis ich was kapiere :smile:

So, also ich hoffe, ich gehe Euch nicht auf die Nerven und Ihr
helft mir noch das letzte Stückchen weiter… :smile:

Ich bin da waterprofed und Nervenstark geworden, Gerichtsvollzieher in der Bude oder „Sie“ macht Schluß oder die Bank sperrt die Karte…
What shells, Lebbe geht weiter *gähn*
Gruß
Reinhard
ps: ich bin ganz sicher waterprofed ist falsch geschrieben, aber es hatte wohl schon Gründe warum ich in Englisch immer miese Noten hatte *ggg

Gruß
Hans

9

Achso, jetzt verstehe ich … klar. — o.w.T.
o.w.T.

10

Oh, Danke erstmal.
Aber das erschlägt mich für heute Abend, muß ich mir morgen angucken. :smile:

Gruß
Hans

11

Hallo Reinhard!

1. Wie starte ich das Makro über einen Button?
Wenn ich einfach den Code über das Button-Kontextmenü ‚Code
anzeigen‘ reinkopiere, läuft das Makro nicht durch.
Fehlermeldung: „Laufzeitfehler 1004: Die Select-Methode konnte
auf das Range-Objekt nicht angewendet werden.“
Aber dort steht ja auch „Private Sub
CommandButtonClick1()“, ist das die falsche Stelle? Oder kann
man per Button nicht über mehrere Tabellenblätter arbeiten?

Der Button ist mir jetzt neu, setze mal in Egenschaften
(Rechtsklick—Eigenschaften) TakeFocusOnClick auf False. May
be es hilft.

Nee, hat leider nichts genützt.

2. Wie kann ich dem Benutzer eine Abfrage stellen?
Ich möchte sicherstellen, daß man nicht vergißt, vor (bzw.
bei) Anlauf des Makros den richtigen Abnehmer auszuwählen.
Also eine Abfrage ala

„Ist der Abnehmer korrekt ausgewählt?“
[Ja] [Nein]

Bei [Ja] soll das Makro weiterlaufen.
Bei [Nein] soll es direkt beendet werden, um den richtigen
Abnehmer auszuwählen und dann das Makro nochmal zu starten.

Sorry, welchen Abnehmer meinst du? Ich brauch manchmal
Extrabelehrung bis ich was kapiere :smile:

Den Abnehmer, der mir meine Produkte abnimmt, Milch, Fleisch, Eier und so Zeug. Hat also nix mit Excel zu tun. :smile:
Es muß halt nur vor Makrostart per Autofilter ausgewählt werden, für wen das Makro überhaupt die Berechnung durchführen soll.

Also völlig Wurst, vergiß den Abnehmer.
Das Problem ist, bei Makro-Beginn den Benutzer zu fragen, ob er eine bestimmte (beliebige) Aktion schon ausgeführt hat. Wenn Ja - Makro läuft weiter, wenn Nein - Makro wird beendet. Wie macht man sowas?

ps: ich bin ganz sicher waterprofed ist falsch geschrieben,
aber es hatte wohl schon Gründe warum ich in Englisch immer
miese Noten hatte *ggg

Keine Ahnung, wir hatten nur Russisch in der Schule und davon kann ich auch nicht mehr genug, um in Rußland zu überleben. :smile:

Gruß
Hans

12

Das Problem ist, bei Makro-Beginn den Benutzer zu fragen, ob
er eine bestimmte (beliebige) Aktion schon ausgeführt hat.
Wenn Ja - Makro läuft weiter, wenn Nein - Makro wird beendet.
Wie macht man sowas?

Hi Hans,
z.B. so:

Option Explicit

Sub tt()
Dim Mldg, Stil, Titel, Antwort
Mldg = "Möchten Sie fortfahren ?"
Stil = vbYesNo + vbCritical + vbDefaultButton2
Titel = "MsgBox-Demonstration" ' Titel definieren.
Antwort = MsgBox(Mldg, Stil, Titel) ' Meldung anzeigen.
If Antwort vbYes Then Exit Sub

' hier gehts weiter bei ja
MsgBox "Huhu"
End Sub

Und wegen dem Fehler 1004, lade mal eine kleine Beispieldatei mit diesem Fehler hoch, bei http://www.badongo.com o.ä. und poste hier den Link.

Gruß
Reinhard

1 „Gefällt mir“
13

Vielen Dank Carsten!
Hab’s eingebaut und es klappt wunderbar. :smile:

Gruß
Hans

14

Auch Dir nochmal vielen Dank Reinhard!
Ich habe Deine Lösung mit Carstens kombiniert damit sich keiner umsonst bemüht hat. :wink:

Und wegen dem Fehler 1004, lade mal eine kleine Beispieldatei
mit diesem Fehler hoch, bei http://www.badongo.com o.ä. und
poste hier den Link.

Das hat sich erledigt. Man muß aus dem „Private Sub …“ des Buttons einfach nur sein eigenes Makro aufrufen und nicht den Code hineinkopieren, dann klappts.

Gruß
Hans