.formula = "=SUM(" & "xxx" verursacht Fehler 400
08.11.2023 16:54:39
TomHoff
Ich habe eine Code geschrieben, der "automatisch" mehrere Tabellen mit Stockwerken und Typen erzeugt. (Als Übersicht für Baustellen)
Diese Tabellen haben Ergebniszeilen nach unten und rechts mit Summen und Tabellentotal unten rechts.
Das habe ich soweit hingekriegt.
Nun möchte ich zum Schluss eine Zelle haben, welche alle Tabellentotal zusammenzählt.
Allerdings läuft der Code auf Fehler, ich konnte auch herausfinden welche Zeile.
Der Fehler befindet sich im letzten Abschnitt, ich habe die Zeile kommentiert.
Anscheinend gefällt Excel das Gleich-Zeichen nicht. Wenn man dies wegnimmt oder die Gesamte Zeile kommentiert, funktioniert der Code.
Ich hoffe jemand von euch kann diese Knacknuss lösen :-)
Freundliche Grüsse
Hier der Code:
Sub ErweitereTabellen()
Dim AnzahlHäuser As Integer
Dim AnzahlTypen As Integer
Dim AnzahlStockwerke As Integer
Dim NeueTabelle As ListObject
Dim NeueSpalte As ListColumn
Dim i As Integer
Dim j As Integer
Dim ZeilenIndex As Integer
Dim ZeilenNamen() As String
Dim ws As Worksheet
Dim Offset As Integer
Dim Tabellennamen() As String
' Eingabeaufforderung für die Anzahl der Häuser
AnzahlHäuser = InputBox("Geben Sie die Anzahl der Häuser ein:", "Anzahl der Häuser", 1)
' Überprüfen, ob gültige Werte eingegeben wurden
If AnzahlHäuser 1 Then
MsgBox "Ungültige Anzahl. Die Tabellen wurden nicht erstellt.", vbExclamation
Exit Sub
End If
' Aktuelles Arbeitsblatt
Set ws = ThisWorkbook.Sheets.Add
' Initialisiere das Tabellennamen-Array
ReDim Tabellennamen(1 To AnzahlHäuser)
' Schleife für jedes Haus
For i = 1 To AnzahlHäuser
' Eingabeaufforderungen für die Anzahl der Stockwerke und Typen pro Haus
AnzahlStockwerke = InputBox("Geben Sie die Anzahl der Stockwerke für Haus " & i & " ein:", "Anzahl der Stockwerke", 1)
AnzahlTypen = InputBox("Geben Sie die Anzahl der Typen für Haus " & i & " ein:", "Anzahl der Typen", 1)
' Überprüfen, ob gültige Werte eingegeben wurden
If AnzahlStockwerke 1 Or AnzahlTypen 1 Then
MsgBox "Ungültige Anzahl für Haus " & i & ". Die Tabelle wurde nicht erstellt.", vbExclamation
Else
' Tabelle erstellen und benennen
Set NeueTabelle = ws.ListObjects.Add(xlSrcRange, ws.Range("A2").Offset(Offset, 0).Resize(AnzahlStockwerke + 1, AnzahlTypen + 2), , xlYes)
Dim Tabellenname As String
Tabellenname = "Haus_" & i
NeueTabelle.Name = Tabellenname
Tabellennamen(i) = Tabellenname ' Speichere den Tabellennamen
' Spaltennamen festlegen
Set NeueSpalte = NeueTabelle.ListColumns(1)
NeueSpalte.Name = "Stockwerk"
For j = 2 To AnzahlTypen + 1
Set NeueSpalte = NeueTabelle.ListColumns(j)
NeueSpalte.Name = "Typ " & (j - 1)
Next j
' Spalte "Total pro Stockwerk" hinzufügen
Set NeueSpalte = NeueTabelle.ListColumns(AnzahlTypen + 2)
NeueSpalte.Name = "Total pro Stockwerk"
' Zeilennamen festlegen
ReDim ZeilenNamen(1 To AnzahlStockwerke)
For j = 1 To AnzahlStockwerke
ZeilenNamen(j) = "Stockwerk " & j
Next j
NeueTabelle.ListColumns(1).DataBodyRange.Value = Application.Transpose(ZeilenNamen)
' Überschrift für das Haus
ws.Cells(1, 1).Offset(Offset, 0).Value = "Haus " & i
' Summe der Typen für jedes Stockwerk
For ZeilenIndex = 1 To AnzahlStockwerke
NeueTabelle.ListColumns(AnzahlTypen + 2).DataBodyRange.Cells(ZeilenIndex, 1).Formula = "=SUM(" & NeueTabelle.ListColumns(2).DataBodyRange.Cells(ZeilenIndex, 1).Resize(1, AnzahlTypen).Address & ")"
Next ZeilenIndex
' Summe der Typen für jedes Stockwerk
For j = 2 To AnzahlTypen + 1
NeueTabelle.ListColumns(j).TotalsCalculation = xlTotalsCalculationSum
Next j
' Ergebniszeile anzeigen
NeueTabelle.ShowTotals = True
' Offset für die nächste Tabelle
Offset = Offset + AnzahlStockwerke + 4 ' Vier Zeilen Abstand
End If
Next i
' Erstelle die Zelle für die Gesamtsumme
ws.Cells(Offset + 4, 1).Value = "Gesamtsumme"
' Erstelle die Formel in der Zelle, um die Totalwerte aller Tabellen zusammenzuzählen
Dim Gesamtformel As String
Gesamtformel = "=Summe(" ' ---- !!!! Das ist die Zeile die den Fehler verursacht !!!!
For i = 1 To AnzahlHäuser
Gesamtformel = Gesamtformel & Tabellennamen(i) & "[[#Ergebnisse];[Total pro Stockwerk]]"
If i AnzahlHäuser Then
Gesamtformel = Gesamtformel & "+"
End If
Next i
Gesamtformel = Gesamtformel & ")"
ws.Cells(Offset + 4, 2).Formula = Gesamtformel
End Sub