Variable Summenformel in VBA

Forumthread: Variable Summenformel in VBA

29.06.2016 16:52:50

TomTom

Hallo Excel-Experten,
ich suche nach einer Möglichkeit, die Standard-Summen-Formel in VBA variabel zu gestalten.
Das Makro macht bisher folgendes: Es ermittelt eine Anzahl von Teilenummern und erstellt daraufhin eine Art Formular. Bei Anzahl = 100 werden 100 "Kästchen" erstellt, bei Anzahl = 4 werden vier Kästchen erstellt (siehe Beispiel).
Darunter soll jetzt die Summe für die Bedarfe gebildet werden.
Folgende Probleme:
- Die zu summierenden Zellen sind getrennt, die Spalte bleibt gleich, die Zeilenanzahl erhöht sich immer um 6. Bspw. B5, B11, B17, B23
- Ich würde gerne die Summenformel verwenden, da Veränderungen in B5, B11, B17, B23 in Echtzeit angezeigt werden sollen und nicht erst nach dem Durchlaufen eines (weiteren) Makros.
- Variabel auch deshalb, weil die zu summierenden Zellen von eins bis viele variieren können.
Meine "Wunschformel" besteht also aus einer Kombination von Array und Summe, bspw.


AnzahlMaterial = 4 'aus vorherigem Makro ermittelt
Zeile = 5
for i = 1 to AnzahlMaterial
cells(26,2).formula = "=SUM(cells(Zeile, 2),cells(Zeile, 2),cells(Zeile, 2),...)
Zeile = Zeile + 6
Next

...bis die Schleife eben abgebrochen wird.
Die Lösung muss natürlich nicht nach diesem Schema aufgebaut sein, es soll einfach nur eine Idee wiedergeben.
Bin um jede Antwort dankbar, bei Bedarf erkläre ich das Problem auch nochmals im Detail.
https://www.herber.de/bbs/user/106622.xlsx

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variable Summenformel in VBA

29.06.2016 18:05:40

Sven

AW: Variable Summenformel in VBA

29.06.2016 18:05:40

Sven

Hallo Tom,
mit ist nicht hundertprozentig klar was genau du machen willst aber mit diesem Code kannst du in variable Zellen variable Summeninhalte schreiben:


statval.Cells(izelleReihe, izelleSpalte).Formula = _
"=Sum(Sort!" & .Range(.Cells(iStartReihe, iStartSpalte), _
.Cells(iEndReihe, iEndSpalte)).Address(0, 0) & ")"

Sort! ist dabei der Sheetname
Hilft dir das?
vg

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um eine variable Summenformel in VBA zu erstellen, kannst Du folgende Schritte befolgen:

Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor in Excel zu öffnen.
Füge ein neues Modul hinzu: Klicke mit der rechten Maustaste auf VBAProject (DeineDatei.xlsm) und wähle Einfügen > Modul.

Schreibe den VBA-Code: Füge den folgenden Code in das Modul ein. Dieser Code erstellt eine Summenformel, die auf variablen Zellen basiert:

Sub VariableSumme()
    Dim AnzahlMaterial As Integer
    Dim Zeile As Integer
    Dim i As Integer

    AnzahlMaterial = 4 ' Anzahl der zu summierenden Materialien
    Zeile = 5 ' Startzeile

    ' Setze die Formel für die Summe in die Zelle B26
    For i = 1 To AnzahlMaterial
        Cells(26, 2).Formula = Cells(26, 2).Formula & " + " & Cells(Zeile, 2).Address(False, False)
        Zeile = Zeile + 6 ' Erhöhe die Zeile um 6 für die nächste Zelle
    Next i

    ' Entferne das erste Pluszeichen
    Cells(26, 2).Formula = Mid(Cells(26, 2).Formula, 4)
End Sub

Führe das Makro aus: Schließe den VBA-Editor und führe das Makro über Entwicklertools > Makros aus.

Häufige Fehler und Lösungen

Fehler: Formel wird nicht korrekt angezeigt
Lösung: Stelle sicher, dass Du die Zellenadresse korrekt angibst. Verwende Cells(Zeile, Spalte).Address, um die Adresse der Zelle zu erhalten.
Fehler: Makro läuft nicht
Lösung: Überprüfe die Sicherheitseinstellungen für Makros in Excel. Gehe zu Datei > Optionen > Sicherheitscenter > Einstellungen für das Sicherheitscenter und aktiviere die Makros.

Alternative Methoden

Eine weitere Möglichkeit, um eine Summenformel in einer Zelle zu schreiben, besteht darin, direkt auf die Zellen zu verweisen. Hier ist ein Beispiel:

statval.Cells(izelleReihe, izelleSpalte).Formula = _
"=SUM(Sort!" & Range(Cells(iStartReihe, iStartSpalte), Cells(iEndReihe, iEndSpalte)).Address(0, 0) & ")"

In diesem Beispiel wird die Formel direkt in die Zelle geschrieben, basierend auf dem angegebenen Bereich.

Praktische Beispiele

Beispiel für eine einfache Summenformel:
```
Cells(26, 2).Formula = "=SUM(B5,B11,B17,B23)"
```
Diese Formel summiert die Werte in den Zellen B5, B11, B17 und B23.
Beispiel mit dynamischen Zellbereichen: Angenommen, Du hast eine Liste von Werten in Spalte B und möchtest die Summe in Zelle B26 anzeigen. Verwende den variablen Bereich wie folgt:
```
Dim letzteZeile As Long
letzteZeile = Cells(Rows.Count, 2).End(xlUp).Row
Cells(26, 2).Formula = "=SUM(B5:B" & letzteZeile & ")"
```

Tipps für Profis

Nutze benannte Bereiche für bessere Lesbarkeit: Statt B5:B10 könntest Du einen benannten Bereich wie Bedarfsbereich erstellen und in Deiner Formel verwenden.
Überlege, wie Du Deine Formeln optimieren kannst, um die Leistung bei großen Datenmengen zu verbessern.
Teste Deine VBA-Makros immer in einer Kopie Deiner Datei, um Datenverlust zu vermeiden.

FAQ: Häufige Fragen

1. Was ist die Summenformel?
Die Summenformel in Excel wird verwendet, um die Summe von Zahlen in einem bestimmten Bereich zu berechnen. Sie ist eine der am häufigsten verwendeten Funktionen in Excel.

2. Wie kann ich die Summenformel in VBA verwenden?
Du kannst die Summenformel in VBA verwenden, indem Du die Formula-Eigenschaft eines Zellobjekts zuweist, wie in den obigen Beispielen gezeigt.

3. Kann ich die Summenformel in Zellen dynamisch ändern?
Ja, Du kannst die Summenformel dynamisch ändern, indem Du die Formel in VBA anpasst, basierend auf den Daten, die sich ändern.

Beliebteste Forumthreads (12 Monate)