VBA - SUMMEWENN Formel vervollständigen

[Mr.Invisible]

Hallo Zusammen,

als Neuling in VBA stehe ich wieder vor einem Problem.

Ich habe eine Tabelle mit einer SUMMEWENN Formel. 
Mit meinem Makro lege ich einen neuen Auftrag in einer neuen Zeile an.

Meine Tabelle sieht so aus.

     |       A      |      B      |    C   |     D    |                    |   H  |     I     |
| 1 |  Auftrag   | Kunde   |  Wert | Status |                                                                      
| 2 | Auftrag 1 | Kunde 1 | 1000 |   LLI    |                    |  LLI  | 21.000 |
| 3 | Auftrag 2 | Kunde 2 | 1000 |   MFG  |                    | MFG | 10.500 |
| 4 | Auftrag 3 | Kunde 1 | 1500 |   DEL   |                    | DEL | 35.000 |
| 5 | 
| 6 | Summe Gesamt | SUMMEWENN |


Mein SummeWenn Formel sieht folgendermaßen aus: 
=SUMME(SUMMEWENN($H$1:$H$3;D2;$I$2:$I$4);SUMMEWENN($H$1:$H$3;D3;$I$2:$I$4);SUMMEWENN$H$1:$H$3;D4;$I$2:$I$4))

Wenn ich jetzt über meinen CommandButton einen neuen Auftrag und eine neue Zeile hinzufüge, verschiebt sich die 6. Zeile zwar nach unten, aber ich weiß nicht wie ich die Formel automatisch aktualisieren kann bzw. wie muss der Code ausschauen, damit dies passiert. 

Hier der Code für die neue Zeile:

Code:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim rng2 As Range

Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

ActiveSheet.Cells(lastrow, "A").EntireRow.Insert
ActiveSheet.Cells(lastrow, "A").Value = TextBox1
ActiveSheet.Cells(lastrow, "B").Value = TextBox2
ActiveSheet.Cells(lastrow, "C").Value = TextBox3
ActiveSheet.Cells(lastrow, "D").Value = TextBox4

End Sub

Ich hoffe ich konnte mein Problem so erklären, dass man es versteht.

Danke & schöne Grüße.

[Käpt'n Blaubär]

Hallo,

wenn ich Dich richtig verstanden habe, dann wäre mein Vorschlag

Füge die Zeile nicht oben, sondern unten ein. Dann gibt es das Problem nicht.

[WillWissen]

Hi,

formatiere deine Tabelle als Tabelle (STRG-T), dann müssten die Formeln weitergeschrieben werden. Bin aber kein VBA-Mensch und kann dir deshalb nicht sagen, ob das Hinzufügen der Zeile per Makro ebenfalls zum gewünschten Erfolg führt.

[Mr.Invisible]

Das Problem was ich habe ist, wenn ich das als Tabelle formatiere dann kann ich die Arbeitsmappe nicht mehr für das gemeinsame Arbeiten freigeben.

[Mr.Invisible]

Hallo,

wenn ich Dich richtig verstanden habe, dann wäre mein Vorschlag

Füge die Zeile nicht oben, sondern unten ein. Dann gibt es das Problem nicht.

Die neue Zeile wird unten eingefügt. Jedoch weiß ich nicht wie ich den Code schreiben muss, damit die SummeWenn Formel die neue Zeile mit einfügt.

[BoskoBiati]

Hi,

dann arbeite ohne Formel und berechne die Summe über das Makro:

Code:

Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

ActiveSheet.Cells(lastrow, "A").EntireRow.Insert
ActiveSheet.Cells(lastrow, "A").Value = TextBox1
ActiveSheet.Cells(lastrow, "B").Value = TextBox2
ActiveSheet.Cells(lastrow, "C").Value = TextBox3
ActiveSheet.Cells(lastrow, "D").Value = TextBox4
loSumme=application.worksheetfunction.sumif(Range("H2:H" & lastrow+1),Range("D2"),Range("I2:I" & lastrow+1))
loSumme=loSumme+application.worksheetfunction.sumif(Range("H2:H" & lastrow+1),Range("D3"),Range("I2:I" & lastrow+1))
loSumme=loSumme+application.worksheetfunction.sumif(Range("H2:H" & lastrow+1),Range("D4"),Range("I2:I" & lastrow+1))
cells(lastrow+3,3)=losumme
End Sub

ungetestet!

[Kuwer]

Hallo,

Mein SummeWenn Formel sieht folgendermaßen aus: 
=SUMME(SUMMEWENN($H$1:$H$3;D2;$I$2:$I$4);SUMMEWENN($H$1:$H$3;D3;$I$2:$I$4);SUMMEWENN$H$1:$H$3;D4;$I$2:$I$4))

- Die Formel ist fehlerhaft
- Warum beginnt der Bereich in Spalte H eine Zeile höher?

Wenn ich jetzt über meinen CommandButton einen neuen Auftrag und eine neue Zeile hinzufüge, verschiebt sich die 6. Zeile zwar nach unten, ...

Das halte ich für ein Gerücht, dass Dein Code das so macht.

Gruß Uwe

[Mr.Invisible]

Wäre auch eine Möglichkeit, aber dann muss ich für jede Zeile den Code erweitern. Und wenn ich eine neue Zeile einfüge wird nichts automatisch erweitert.

Ich versuch nochmals mein Problem genau zu erklären. Mein erster Post war nur ein Beispiel.

Meine Tabelle zeigt alle aktuellen Aufträge. Im Bereich N3 bis BI3 werden die Arbeitsfortschritte angezeigt (LLI; DEL; MFG), in Spalte K3:K19 werden die einzelnen Arbeitsfortschritte aufgelistet und in Spalte L3:L19 werden die Werte für den jeweiligen Arbeitsfortschritt aufgelistet und im Bereich N22 bis BI22 liegt die SUMMEWENN-Formel. 

Lege ich nun über den CommandButton einen neuen Auftrag an werden im Bereich N bis BI die Arbeitsschritte des neuen Auftrags eingefügt. Somit verschiebt sich die SUMMEWENN-Formel in den Bereich N23 bis BI23.

Die SUMMEWENN-Formel sieht folgendermaßen aus:

=SUMME(SUMMEWENN($K$3:$K$19;N3;$L$3:$L$19); ... ;SUMMEWENN($K$3:$K$19;N20;$L$3:$L$19))

Hier weiß ich eben nicht was ich machen muss damit am Schluss der Formel eine weiter SummeWenn-Formel eingefügt wird für den neuen Auftrag !?
D.h. es müsste dann bei einer neuen Zeile so aussehen: 
=SUMME(SUMMEWENN($K$3:$K$19;N3;$L$3:$L$19); ... ;SUMMEWENN($K$3:$K$19;N20;$L$3:$L$19);SUMMEWENN($K$3:$K$19;N21;$L$3:$L$19))

Bei zwei neuen Zeilen so:
=SUMME(SUMMEWENN($K$3:$K$19;N3;$L$3:$L$19); ... ;SUMMEWENN($K$3:$K$19;N20;$L$3:$L$19);SUMMEWENN($K$3:$K$19;N21;$L$3:$L$19);SUMMEWENN($K$3:$K$19;N22;$L$3:$L$19))

Und so weiter und so weiter.

Ich hoffe ich hab es jetzt so erklärt, dass es verständlich ist.

[Mr.Invisible]

Hallo,

- Die Formel ist fehlerhaft
- Warum beginnt der Bereich in Spalte H eine Zeile höher?

Das halte ich für ein Gerücht, dass Dein Code das so macht.

Gruß Uwe

Weil ich hier eine Formel geschrieben hab die nur als Beispiel dient, ist mir leider ein Fehler unterlaufen.

Und doch, mein Code fügt zwischen dem letzten Auftrag und der Gesamtsumme eine neue Zeile ein. Somit verschiebt sich die Zeile eine Zeile nach unten.

[Mr.Invisible]

Konnte in einem anderem Forum eine Lösung für mein Problem finden. Poste hier den richtigen Code falls irgendwann mal jemand vor dem selbem Problem steht.

Code:

i = 4
calc = "=SUM(SUMIF($K$3:$K$19,N3,$L$3:$L$19),"
Do Until i = lastrow
   calc = calc & "SUMIF($K$3:$K$19,N" & i & ",$L$3:$L$19),"
   i = i + 1
Loop

ActiveSheet.Range("N" & lastrow + 1 & ":BI" & lastrow + 1).Value = calc & ")"