PDA

Vollständige Version anzeigen : Summewenn in VBA


d3nz
04.10.2016, 16:58
Hallo zusammen,

ich möchte quasi eine summewenn Funktion in VBA lösen.

Zuserst zähle ich wie viele Zeilen in Tabelle3 vorhanden sind (bzw. ausgegüllt sind). Dann summiere ich alle Werte aus Spalte J deren Zeile in Spalte G den Wert 100 hat.

So zumindest der Plan aber es wird mir ein Laufzeitfehler 13 unverträgliche typen ausgegeben.

Folgend mein Code:

Sub Sum100()

Dim zeile As Long
Dim zeileMax As Long

With Tabelle3

zeileMax = Tabelle3.UsedRange.Rows.Count

End With

sumA = Application.WorksheetFunction.SumIf(Tabelle4.Range("J2:J" & zeileMax), Tabelle3.Range("G2:G" & zeileMax), 100)

Tabelle4.Cells(4, 5) = zeileMax
Tabelle4.Cells(5, 5) = sumA


End Sub



Gruß

Hajo_Zi
04.10.2016, 18:26
ich würde mal vermute auf 23 Tabelle geht das nicht.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

Crazy Tom
04.10.2016, 18:33
Hallo

zum einen hast du sumA nicht deklariert - sollte Double sein
zum zweiten sind die Argumente in der Funktion vertauscht - siehe Online-Hilfe
und zum dritten... einmal Tabelle4 und einmal Tabelle3 in der Funktion???

so habe ich es getestet
sumA = Application.WorksheetFunction.SumIf(Tabelle3.Range("J2:J" & zeileMax), 100, Tabelle3.Range("G2:G" & zeileMax))

MfG Tom

d3nz
05.10.2016, 07:46
Guten Morgen ihr Beiden,

@Hajo_Zi: Leider verstehe ich nicht was du meinst.

@Crazy Tom: Stimmt (vertauscht) ich habe in der Doku bei SumIfs geschaut.
Die Deklaration habe ich hinzugefügt, war jetzt aber denke ich nicht kriegsentscheidend. Trotzdem danke für den Hinweis.
Einmal Tabelle 3 und einmal Tabelle 4 ist schon richtig. In Tabelle 3 werden regelmäßig die Daten aktualisiert (von eine Drittprogramm neu eingespielt wenn ein Datensatz hinzukam oder sich verändert hat) und diese möchte ich dann in Tabelle 4 auswerten.

Nachdem die Reihenfolge der Argumente richtig ist, bekomme ich zwar keinen Laufzeitfehler mehr aber das angezeigt Ergebnis ist wieder nur 0 :(



EDIT: So sieht nun der angepasste Code aus:

sumA = Application.WorksheetFunction.SumIf(Tabelle3.Range("G2:G" & zeileMax), 100, Tabelle4.Range("J2:J" & zeileMax))

Crazy Tom
05.10.2016, 08:28
Hallo

hast du im Nachhinein die Tabelle4 eingefügt?
in meinem Test wurde so die eingefügte Tabelle4 zu der Tabelle mit dem Codenamen Tabelle1 und das Worksheet Tabelle3 bekam den Codename Tabelle4
du sprichst in deinem Makro aber die Tabellen mit dem Codenamen an
da hat es in meinem Test mit xl2003 nicht hingehauen
bei der Referenzierung mit dem Tabellennamen hat es dann geklappt
dann sieht der Code bei mir so aus

Sub Sum100()
Dim zeile As Long
Dim zeileMax As Long
Dim sumA As Double
With Sheets("Tabelle3")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row
MsgBox zeileMax
sumA = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 100, Sheets("Tabelle4").Range("J2:J" & zeileMax))
MsgBox sumA
Sheets("Tabelle4").Cells(4, 5) = zeileMax
Sheets("Tabelle4").Cells(5, 5) = sumA
End With
End Sub

MfG Tom

d3nz
05.10.2016, 09:05
Funktioniert bei mir immer noch nicht. (Es wird immer noch 0 ausgegeben.)

Das ich die richtigen Tabellen anspreche kann ich ja auch oben links nochmal sehen, da werden ja alle tabellenblätter angezeigt und wie sie heißen. Das hatte ich schon überprüft.

Aber zwischendurch mal was Grundsätzliches.
Ich habe ein Modul angelegt und darin schreibe ich die Subs.
Diese rufe ich dann im Tabellenblatt unter dem Sub Worksheet_SelectionChange mit dem Befehl Call auf, richtig?
Weil bei mir klappt das bspw. nicht mit .Cells oder .Range ich muss immer die Tabelle davor schreiben also bspw. Tabelle3.Cells oder Sheets("Rohdaten").Cells

Hier nochmal mein aktueller Code:
Sub Sum100()

Dim zeileMax As Long
Dim sumA As Double

With Sheets("Rohdaten")
zeileMax = Sheets("Rohdaten").Cells(Sheets("Rohdaten").Rows.Count, 7).End(x1Up).Row
MsgBox zeileMax
sumA = Application.WorksheetFunction.SumIf(Sheets("Rohdaten").Range("G2:G" & zeileMax), 100, Sheets("Auswertung (Rohdaten)").Range("J2:J" & zeileMax))
MsgBox sumA
End With

End Sub

Crazy Tom
05.10.2016, 09:20
Hallo

kann ich nicht nachvollziehen

MfG Tom

d3nz
05.10.2016, 10:47
Oh man ich trau mich garnicht es zu sagen :rolleyes:

Du hattest die Lösung schon in deinem ersten Beitrag - Tabelle 3 und Tabelle 4. Die summewenn-Funktion soll natürlich nur in einer Tabelle stattfinden. Geschrieben werden soll das Ganze nur in der Anderen.

Also so: sumA = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 100, .Range("J2:J" & zeileMax))

Vielen Dank für deine Mühen! :)

Crazy Tom
05.10.2016, 10:50
Hallo

fein dass es jetzt passt
da mach ich mir jetzt gemütlich ein Piefchen an ;-)

MfG Tom

d3nz
05.10.2016, 11:07
Mach das.
Und ich versuche jetzt mal das Ganze in eine Case Struktur einzubauen.
Habe nämlich nicht nur das Kriterium 100 sondern noch 90 weitere Zahlen. :grins:

Gruß

EDIT: Stelle grade fest, das ist wohl Quatsch und geht wohl genauso schnell wenn ich das so mache:

Sub SumGut()

Dim zeileMax As Long
Dim sum100 As Double
Dim sum101 As Double
Dim sum102 As Double

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row

sum100 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 100, .Range("J2:J" & zeileMax))
sum101 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 101, .Range("J2:J" & zeileMax))
sum102 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 102, .Range("J2:J" & zeileMax))

Tabelle1.Cells(2, 4) = sum100
Tabelle1.Cells(3, 4) = sum101
Tabelle1.Cells(4, 4) = sum102
End With

End Sub

d3nz
05.10.2016, 13:18
Hallo ich bins nochmal :D

Es wird ziemlich viel (vom ähnlichem) Code :holy:

Und ich bin noch nicht fertig ^^

Sub SumGut()

Dim zeileMax As Long
Dim sum100, sum101, sum102, sum103, sum104, sum105 As Double

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row

sum100 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 100, .Range("J2:J" & zeileMax))
sum101 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 101, .Range("J2:J" & zeileMax))
sum102 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 102, .Range("J2:J" & zeileMax))
sum103 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 103, .Range("J2:J" & zeileMax))
sum104 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 104, .Range("J2:J" & zeileMax))
sum105 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 105, .Range("J2:J" & zeileMax))
sum106 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 106, .Range("J2:J" & zeileMax))
sum107 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 107, .Range("J2:J" & zeileMax))
sum108 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 108, .Range("J2:J" & zeileMax))
sum109 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 109, .Range("J2:J" & zeileMax))
sum110 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 110, .Range("J2:J" & zeileMax))
sum111 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 111, .Range("J2:J" & zeileMax))

Tabelle1.Cells(2, 4) = sum100 '(Zeile, Spalte)
Tabelle1.Cells(3, 4) = sum101
Tabelle1.Cells(4, 4) = sum102
Tabelle1.Cells(5, 4) = sum103
Tabelle1.Cells(6, 4) = sum104
Tabelle1.Cells(7, 4) = sum105
Tabelle1.Cells(8, 4) = sum106
Tabelle1.Cells(9, 4) = sum107
Tabelle1.Cells(10, 4) = sum108
Tabelle1.Cells(11, 4) = sum109
Tabelle1.Cells(12, 4) = sum110
Tabelle1.Cells(13, 4) = sum111
End With

End Sub

Sub SumSchrott()

Dim zeileMax As Long
Dim sum201, sum202, sum203, sum204, sum205, sum206, sum207, sum270, sum280, sum301, sum302, sum303, sum304, _
sum305, sum306, sum308, sum309, sum310, sum311, sum312, sum313, sum314, sum315, sum400, sum401 As Double

With Sheets("Rohdaten")
zeileMax = .Cells(.Rows.Count, 7).End(xlUp).Row

sum201 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 201, .Range("J2:J" & zeileMax))
sum202 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 202, .Range("J2:J" & zeileMax))
sum203 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 203, .Range("J2:J" & zeileMax))
sum204 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 204, .Range("J2:J" & zeileMax))
sum205 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 205, .Range("J2:J" & zeileMax))
sum206 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 206, .Range("J2:J" & zeileMax))
sum207 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 207, .Range("J2:J" & zeileMax))
sum270 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 270, .Range("J2:J" & zeileMax))
sum280 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 280, .Range("J2:J" & zeileMax))
sum301 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 301, .Range("J2:J" & zeileMax))
sum302 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 302, .Range("J2:J" & zeileMax))
sum303 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 303, .Range("J2:J" & zeileMax))
sum304 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 304, .Range("J2:J" & zeileMax))
sum305 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 305, .Range("J2:J" & zeileMax))
sum306 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 306, .Range("J2:J" & zeileMax))
sum308 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 308, .Range("J2:J" & zeileMax))
sum309 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 309, .Range("J2:J" & zeileMax))
sum310 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 310, .Range("J2:J" & zeileMax))
sum311 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 311, .Range("J2:J" & zeileMax))
sum312 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 312, .Range("J2:J" & zeileMax))
sum313 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 313, .Range("J2:J" & zeileMax))
sum314 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 314, .Range("J2:J" & zeileMax))
sum315 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 315, .Range("J2:J" & zeileMax))
sum400 = Application.WorksheetFunction.SumIf(.Range("G2:G" & zeileMax), 400, .Range("J2:J" & zeileMax))


Tabelle1.Cells(13, 4) = sum201
Tabelle1.Cells(14, 4) = sum202
Tabelle1.Cells(15, 4) = sum203
Tabelle1.Cells(16, 4) = sum204
Tabelle1.Cells(17, 4) = sum205
Tabelle1.Cells(18, 4) = sum206
Tabelle1.Cells(19, 4) = sum207
Tabelle1.Cells(20, 4) = sum270
Tabelle1.Cells(21, 4) = sum280
Tabelle1.Cells(22, 4) = sum301
Tabelle1.Cells(23, 4) = sum302
Tabelle1.Cells(24, 4) = sum303
Tabelle1.Cells(25, 4) = sum304
Tabelle1.Cells(26, 4) = sum305
Tabelle1.Cells(27, 4) = sum306
Tabelle1.Cells(28, 4) = sum308
Tabelle1.Cells(29, 4) = sum309
Tabelle1.Cells(30, 4) = sum310
Tabelle1.Cells(31, 4) = sum311
Tabelle1.Cells(32, 4) = sum312
Tabelle1.Cells(33, 4) = sum313
Tabelle1.Cells(34, 4) = sum314
Tabelle1.Cells(35, 4) = sum315
Tabelle1.Cells(36, 4) = sum400
End With

End Sub

Erst dachte ich mit einer For-Schleife ginge es nicht weil ich ja nicht 100 bis 900 habe sondern zwischendurch einzelne zahlen oder Bereiche ausgelassen sind.
Aber man kann ja eigentlich ein Array aus den 91 Zahlen machen und dann eine For-Schleife mit dem Array durchlaufen lassen oder?

Wäre das abgesehen von der Optik sinnvoller? Also schneller und/oder ressourcenschonender?