Microsoft Excel

Herbers Excel/VBA-Archiv

Tabelle mit Makro erweitern

Betrifft: Tabelle mit Makro erweitern von: Andi
Geschrieben am: 20.08.2014 13:00:13

Hallo,

ich habe eine Tabelle mit folgender Struktur:

Überschrift 1 Überschrift 2
Wert 1 Wert 1
Wert 2 Wert 2
Summe Summe

Jetzt würde ich gerne durch ein Makro eine neue Zeile (Wert 3) einfügen, der das Format und die Formeln (aber nicht die Zellenwerte!) der 1ten Teile übernimmt und auch in die Summe einbezogen wird.

  

Betrifft: AW: Tabelle mit Makro erweitern von: Werner
Geschrieben am: 20.08.2014 13:12:03

Hallo Andi,

kuckst du.

https://www.herber.de/bbs/user/92201.xlsm

Gruß Werner


  

Betrifft: AW: Tabelle mit Makro erweitern von: Jürgen V.
Geschrieben am: 20.08.2014 13:15:46

Hallo Andi,

wirf doch einmal einen Blick auf das Thema "Tabellen". Wenn Du Deine Wertetabelle mit START - ALS TABELLE FORMATIEREN in eine Tabelle umwandelst, erhältst Du nicht nur eine Formatierung, sondern genau das Verhalten, das Du beschreibst. Für die Summe benötigst Du keine Summe()-Funktion mehr, sondern Du aktiviert nach Klick in die Tabelle über TABELLENTOOLS die Option ERGEBNISZEILE und kannst dann je Spalte festlegen, was in der Ergebniszeile erscheinen soll (z. B. die Summe der Spalte)

Gruß, Jürgen


  

Betrifft: AW: Tabelle mit Makro erweitern von: Andi
Geschrieben am: 20.08.2014 15:02:13

Schon einmal vielen Dank(!!), aber das ist beides nicht so ganz was ich mir vorstelle.
Hier mal ein Bild von der Tabelle:



Was ich will: Wenn ich auf einen Button drücke (Makro), dann soll die Zeile 7 und 8 einfach unten drunter noch einmal leer (nur mit den Formeln) und am besten mit einer fortlaufenden Nummer aufgeführt werden.

Habe es versucht mit "Makro aufzeichnen" was allerdings auch nicht wirklich zu einem zufriedenstellenden Ergebnis geführt hat.


  

Betrifft: AW: Tabelle mit Makro erweitern von: Werner
Geschrieben am: 20.08.2014 15:26:26

Hallo Jürgen,

lade doch mal die Excel-Tabelle hoch.

Werner


  

Betrifft: AW: Tabelle mit Makro erweitern von: Andi
Geschrieben am: 20.08.2014 15:45:41

Habe die Überschriften und andere Tabellenblätter rausgenommen, deshalb fehlen Teilweise Bezüge. Aber das tut ja nichts zur Sache.

https://www.herber.de/bbs/user/92204.xlsx


  

Betrifft: AW: Tabelle mit Makro erweitern von: Werner
Geschrieben am: 21.08.2014 19:22:41

Hallo Andi,

hab heute morgen mal drüber geschaut. Ist, zumindest für mich, nicht ganz einfach wegen verbundener Zellen. Werd mich aber weiter dran versuchen und mich melden.
Ich stell den Beitrag mal offen, vielleicht schaut ja ein Profi mal drüber.

Werner


  

Betrifft: AW: Tabelle mit Makro erweitern von: fcs
Geschrieben am: 23.08.2014 10:58:59

Hallo Andi,

eine Beispieldatei mit Dummydaten wäre schon besser gewesen.
Das Makro wird durch die verbundenen Zellen schon komplizierter. Zusätzlich müssen die Summenformeln per Makro angepasst werden, da sich die Zelenbezüge nicht automatisch anpassen.
Achte bei deinen Formeln darauf, dass du die Zellbezüge ggf. absolut setzen muss, wenn du im Tabellenbereich in Formeln Zellen aus dem oberen Tabellenbereich verwendest.

Gruß
Franz

Sub Zeile7_8_Kopieren()
    Dim wks As Worksheet
    Dim ZeileSumme As Long, lngNr As Long, Zeile As Long
    Dim Spalte As Long
    Set wks = ActiveSheet
    
    With wks
        'Zeile mit Summen ermitteln
        Zeile = 7
        lngNr = 1
        Do Until .Cells(Zeile, 1) = "Summenzeile"
            Zeile = Zeile + 2
            lngNr = lngNr + 1
        Loop
        ZeileSumme = Zeile
        'Zeilen 7+8 kopieren und oberhalb der Summenzeile einfügen
        .Range(.Rows(7), .Rows(8)).Copy
        .Rows(ZeileSumme).Insert
        ZeileSumme = ZeileSumme + 2
        
        'fortlaufende Nr. eintragen und in Zellen ohne Formel Inhalte löschen
        For Spalte = 1 To 25
            With .Cells(Zeile, Spalte)
                Select Case Spalte
                    Case 1
                        .Value = lngNr
                    Case 2 To 5, 10 To 13, 15 To 17, 19 To 21, 25
                        'verbundene Zellen
                        .MergeArea.ClearContents
                    Case 6 To 8
                        '2 Zeilen, Zellen nicht verbunden
                        .ClearContents
                        .Offset(1, 0).ClearContents
                    Case 9, 14, 18, 22, 23, 24
                        'Formeln nicht löschen
                End Select
            End With
        Next
        
        'in Summenzeile die Formeln anpassen
        For Spalte = 1 To 25
            With .Cells(ZeileSumme, Spalte)
                Select Case Spalte
                    Case 1 To 4
                        'keine Formel
                    Case 5 To 7, 9 To 11, 14, 15, 18, 19, 22 To 25
                        'Summenformel
                        .FormulaR1C1 = "=SUM(R[" & (-ZeileSumme + 7) & "]C:R[-1]C)"
                    Case 20
                        'Sonderformel =WENN((SUMME(T7:T8)-I2)<0;0;SUMME(T7:T8)-I2)
                        .FormulaR1C1 = "=IF((SUM(R[" & (-ZeileSumme + 7) _
                            & "]C:R[-1]C) - R2C9) < 0, 0, SUM(R[" _
                            & (-ZeileSumme + 7) & "]C:R[-1]C) - R2C9)"
                    Case 21
                        'Sonderformel =WENN((SUMME(U7:U8)-J2)<0;0;SUMME(U7:U8)-J2)
                        .FormulaR1C1 = "=IF((SUM(R[" & (-ZeileSumme + 7) _
                            & "]C:R[-1]C) - R2C10) < 0, 0, SUM(R[" 
                            & (-ZeileSumme + 7) & "]C:R[-1]C) - R2C10)"
                End Select
            End With
        Next
        
    End With
    Cells(Zeile, 2).Select
End Sub



  

Betrifft: AW: Tabelle mit Makro erweitern von: Werner
Geschrieben am: 24.08.2014 17:27:35

Hallo Franz,

von Andi kommt wohl nix.... schade.

Dein Code ist Hammer, wobei ich noch nicht so wirklich alles verstehe, bin halt Anfänger. Kleine Anmerkung am Rande, in deinem Code fehlt im unteren Bereich bei einem Zeilenumbruch ein Unterstrich.

Ich habe mich auch an der Sache versucht, wahrscheinlich (wenn ich deinen Code so sehe) ziemlich umständlich (auf meine Weise halt eben) aber es funktioniert.

Stelle meine Ergüsse auch mal online (aber bitte keinen Lachkrampf kriegen).

https://www.herber.de/bbs/user/92265.xlsm

Gruß Werner


  

Betrifft: AW: Tabelle mit Makro erweitern von: Andi
Geschrieben am: 25.08.2014 10:42:14

Hallo Franz,

vielen Dank für deine Mühe!!
Ich habe zwischenzeitlich allerdings die verbundenen Zellen gelöscht.
Jetzt habe ich versucht den Code entsprechend anzupassen, allerdings funktioniert er nur bei der ersten zu kopierenden Zeile.

Hier die geänderte Version:

Sub Neue_Zeile()
    ActiveSheet.Unprotect Password:="123"
    Dim wks As Worksheet
    Dim ZeileSumme As Long, lngNr As Long, Zeile As Long
    Dim Spalte As Long
    Set wks = ActiveSheet
    
    With wks
        'Zeile mit Summen ermitteln
        Zeile = 7
        lngNr = 1
        Do Until .Cells(Zeile, 1) = "Summenzeile"
            Zeile = Zeile + 2
            lngNr = lngNr + 1
        Loop
        ZeileSumme = Zeile
        'Zeilen 7+8 kopieren und oberhalb der Summenzeile einfügen
        .Rows(8).Copy
        .Rows(ZeileSumme).Insert
        ZeileSumme = ZeileSumme + 2
        
        'fortlaufende Nr. eintragen und in Zellen ohne Formel Inhalte löschen
        For Spalte = 1 To 25
            With .Cells(Zeile, Spalte)
                Select Case Spalte
                    Case 1
                        .Value = lngNr
                    Case 2 To 5, 10 To 13, 15 To 17, 19 To 21, 25
                        'verbundene Zellen
                        .MergeArea.ClearContents
                    Case 6 To 8
                        '2 Zeilen, Zellen nicht verbunden
                        .ClearContents
                        .Offset(1, 0).ClearContents
                    Case 9, 14, 18, 22, 23, 24
                        'Formeln nicht löschen
                End Select
            End With
        Next
        
        'in Summenzeile die Formeln anpassen
        For Spalte = 1 To 25
            With .Cells(ZeileSumme, Spalte)
                Select Case Spalte
                    Case 1 To 4
                        'keine Formel
                    Case 5 To 7, 9 To 11, 14, 15, 18, 19, 22 To 25
                        'Summenformel
                        .FormulaR1C1 = "=SUM(R[" & (-ZeileSumme + 7) & "]C:R[-1]C)"
                    Case 20
                        'Sonderformel =WENN((SUMME(T7:T8)-I2)<0;0;SUMME(T7:T8)-I2)
                        .FormulaR1C1 = "=IF((SUM(R[" & (-ZeileSumme + 7) _
                            & "]C:R[-1]C) - R2C9) < 0, 0, SUM(R[" _
                            & (-ZeileSumme + 7) & "]C:R[-1]C) - R2C9)"
                    Case 21
                        'Sonderformel =WENN((SUMME(U7:U8)-J2)<0;0;SUMME(U7:U8)-J2)
                        .FormulaR1C1 = "=IF((SUM(R[" & (-ZeileSumme + 7) _
                            & "]C:R[-1]C) - R2C10) < 0, 0, SUM(R[" _
                            & (-ZeileSumme + 7) & "]C:R[-1]C) - R2C10)"
                End Select
            End With
        Next
        
    End With
    Cells(Zeile, 2).Select
    ActiveSheet.Protect Password:="123"
End Sub



  

Betrifft: AW: Tabelle mit Makro erweitern von: Andi
Geschrieben am: 25.08.2014 10:49:03

Da war ich etwas voreilig. Habe es selbst hinbekommen!!

Vielen Dank :)

    With wks
        'Zeile mit Summen ermitteln
        Zeile = 7
        lngNr = 1
        Do Until .Cells(Zeile, 1) = "Summenzeile"
            Zeile = Zeile + 1
            lngNr = lngNr + 1



  

Betrifft: AW: Tabelle mit Makro erweitern von: Andi
Geschrieben am: 26.08.2014 07:46:11

Ich habe noch eine Frage:

Ich würde mir gerne bei "Km-Stand" in der Summenzeile den letzten Wert in der Liste oben drüber anzeigen lassen. Habe das Formelmäßig so gelöst:

=INDIREKT("V"&VERGLEICH(0;V1:V19;-1))
Allerdings weiß ich nicht, wie ich das in den VBA Code einbaue.


 

Beiträge aus den Excel-Beispielen zum Thema "Tabelle mit Makro erweitern"