Hallo
ich habe ein mittelschweres Problem.
und habe ich so ein ähnliches blatt
Haus1 Tuer 100€
Haus1 Fenster 50€
Haus3 Tuer 100€
Haus2 Tuer 100€
Haus1 Tuer 100€
ich möchte das eine auflistung entsteht mit
Haus1 Tuer 200€
Haus1 Fenster 50€
Haus2 Tuer 100€
Haus3 Tuer 100€
alles zusammenaddiert was Haus1 Tuer ist und somit 200€ errechnet.
ich hoffe ich habe es etwas verständlich rüber gebracht was ich meine.
Danke schon mal
Gruß David
Hallo David,
Geht es hier um Excel-VBA? Bitte immer mit angeben!
Wenn ja, nehme ich an du hast 3 Spalten oder? (Haus, Gegenstand, Preis)
Sollen die Werte geordnet werden, also erst alle Gegenstände in Haus1, dann alle in Haus2, etc. oder ist die Reihenfolge egal?
Das Ergebnis würde ich einfach in ein anderes Sheet schreiben, oder schwebt dir da was anderes vor?
Achja und kennst du dich mit VBA schon ein bischen aus?
Gruß Phil
Hi Phil
ja es geht um Excel-VBA
Ja es sind 3 Spalten Haus/Gegenstand/Preis
JA es sollen alle in Haus1 angezeigt werden oder wie zum beispiel auch alle Fenster zusammengefasst von haus1 und dann weiter mit haus2 …
ja in ein neues Sheet ist völlig okay
Ja ich kenne mich schon etwas aus, nur dieses Problem ist doch etwas schwieriger.
Vielen Dank erst mal
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo David,
da lag ich ja immer richtig mit meinen Einschätzungen! 
Sorry für die vielen Fragen, hier ist dafür die Lösung:
Sub Sortieren()
Dim MaxRow As Long
Dim ArrSort() As String
Dim lRow As Long
Dim HausNr As Long
Dim GegNr As Long
MaxRow = Sheets(1).Cells(Sheets(1).Rows.Count, 1).End(xlUp).Row
ReDim ArrSort(1 To MaxRow, 0 To MaxRow, 0 To 1)
For lRow = 1 To MaxRow
For HausNr = 1 To MaxRow
If ArrSort(HausNr, 0, 0) = Sheets(1).Cells(lRow, 1) Or ArrSort(HausNr, 0, 0) = "" Then
If ArrSort(HausNr, 0, 0) = "" Then ArrSort(HausNr, 0, 0) = Sheets(1).Cells(lRow, 1)
For GegNr = 1 To MaxRow
If ArrSort(HausNr, GegNr, 0) = Sheets(1).Cells(lRow, 2) Or ArrSort(HausNr, GegNr, 0) = "" Then
If ArrSort(HausNr, GegNr, 0) = "" Then ArrSort(HausNr, GegNr, 0) = Sheets(1).Cells(lRow, 2)
If ArrSort(HausNr, GegNr, 1) = "" Then ArrSort(HausNr, GegNr, 1) = 0
ArrSort(HausNr, GegNr, 1) = CDbl(ArrSort(HausNr, GegNr, 1)) + CDbl(Sheets(1).Cells(lRow, 3))
Exit For
End If
Next GegNr
Exit For
End If
Next HausNr
Next lRow
lRow = 1
For HausNr = 1 To MaxRow
If ArrSort(HausNr, 0, 0) = "" Then Exit For
For GegNr = 1 To MaxRow
If ArrSort(HausNr, GegNr, 0) = "" Then Exit For
Sheets(2).Cells(lRow, 1) = ArrSort(HausNr, 0, 0)
Sheets(2).Cells(lRow, 2) = ArrSort(HausNr, GegNr, 0)
Sheets(2).Cells(lRow, 3) = ArrSort(HausNr, GegNr, 1)
lRow = lRow + 1
Next GegNr
Next HausNr
End Sub
Gruss Phil
Hi Phil
erst einmal vielen Dank dafür
ich habe es mir entsprechend angepasst, habe nur noch ein problem
ich bekomme einen fehler mit typenunverträglich
und zwar wenn ich diese Zeile
ArrSort(HausNr, GegNr, 1) = CDbl(ArrSort(HausNr, GegNr, 1)) + CDbl(ws.Cells(lRow, 4))
in
ArrSort(HausNr, GegNr, 1) = CStr(ArrSort(HausNr, GegNr, 1)) + CStr(ws.Cells(lRow, 4))
umschreibe und
Dim ArrSort() As String
steht geht es ganz gut, nur die werte die eigentlich zusammen gerechnet werden sollen stehen nur hintereinander.
habe schon versucht ein Long oder Integer draus zu machen nur dann kommt der fehler schon hier
If ArrSort(HausNr, 0, 0) = ws.Cells(lRow, 2) Or ArrSort(HausNr, 0, 0) = „“ Then
hoffe du kannst mir dabei auch noch helfen
Vielen Dank
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo David,
ich bekomme einen fehler mit typenunverträglich
Wahrscheinlich hast du folgende Zeile gelöscht oder falch „Angepasst“:
If ArrSort(HausNr, GegNr, 1) = "" Then ArrSort(HausNr, GegNr, 1) = 0
Dann kommt ein Fehler weil CDbl("") nicht geht. ("" ist nicht numerisch)
und zwar wenn ich diese Zeile
ArrSort(HausNr, GegNr, 1) = CDbl(ArrSort(HausNr, GegNr, 1)) +
CDbl(ws.Cells(lRow, 4))
in
ArrSort(HausNr, GegNr, 1) = CStr(ArrSort(HausNr, GegNr, 1)) +
CStr(ws.Cells(lRow, 4))
umschreibe und
Dim ArrSort() As String
steht geht es ganz gut, nur die werte die eigentlich zusammen
gerechnet werden sollen stehen nur hintereinander.
klar, mit Strings kann eben nicht gerechnet werden, die sind ja für Texte zuständig.
Result = String1 + String2 ist übrigens das selbe wie
Result = String1 & String2!
oder
Result = „Hal“ + „lo“
=> Wert in Result: „Hallo“
(genau aus diesem Grund verwende ich auch CDbl, die Werte sind davor ja Strings {aufgrund Dim ArrSort() as String})
habe schon versucht ein Long oder Integer draus zu machen nur
dann kommt der fehler schon hierIf ArrSort(HausNr, 0, 0) = ws.Cells(lRow, 2) Or
ArrSort(HausNr, 0, 0) = „“ Then
Hier versuchst du dann, einen String (z.B. „Haus1“) an eine Variable des Typs Integer bzw. Long zu übergeben, das geht nicht!
Hoffe das ist dir jetzt alles klarer geworden.
Gruß Phil
Hi
ja habe es jetzt verstanden und fehler auch gefunden
das iRow fängt von ganz oben an, darf es bei mir ab erst ab zeile 3 und das war der fehler.
Sag mal kann man es auch so machen das zum Beispiel das Haus1 nicht in jeder zeile neu steht, sondern nur einmal und dann wenn Haus2 anfängt das das auch nur einmal steht z.b. in zeile 10, wegen der übersichtlichkeit.also haus1 steht 10 mal da will es aber nur einmal angezeigt bekommen.
Dank
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hi
ja habe es jetzt verstanden und fehler auch gefunden
Super!
Sag mal kann man es auch so machen das zum Beispiel das Haus1
nicht in jeder zeile neu steht, sondern nur einmal und dann
wenn Haus2 anfängt das das auch nur einmal steht z.b. in zeile
10, wegen der übersichtlichkeit.also haus1 steht 10 mal da
will es aber nur einmal angezeigt bekommen.
Ja, am einfachsten geht es, wenn du hier…
> lRow = 1
> For HausNr = 1 To MaxRow
> If ArrSort(HausNr, 0, 0) = "" Then Exit For
> For GegNr = 1 To MaxRow
> If ArrSort(HausNr, GegNr, 0) = "" Then Exit For
> **Sheets(2).Cells(lRow, 1) = ArrSort(HausNr, 0, 0)**
> Sheets(2).Cells(lRow, 2) = ArrSort(HausNr, GegNr, 0)
> Sheets(2).Cells(lRow, 3) = ArrSort(HausNr, GegNr, 1)
> lRow = lRow + 1
> Next GegNr
> Next HausNr
…die Zeile
Sheets(2).Cells(lRow, 1) = ArrSort(HausNr, 0, 0)
zu
If GegNr = 1 Then Sheets(2).Cells(lRow, 1) = ArrSort(HausNr, 0, 0)
änderst.
Gruß Phil
Excel Vba: Teilsummen aufaddieren per Collection
Hi David,
du hast ja schon eine Lösung, aber ich habe mich auch daran versucht.
Aus der Tabelle in Blatt1:
Tabelle:
F:\[SummeMitCollection.xls]!Tabelle1
│ A │ B │ C │
──┼───────┼─────────┼────────┤
1 │ Geb │ Teil │ Betrag │
2 │ Haus1 │ Tuer │ 100 │
3 │ Haus1 │ Fenster │ 50 │
4 │ Haus3 │ Tuer │ 100 │
5 │ Haus2 │ Tuer │ 100 │
6 │ Haus1 │ Tuer │ 100 │
7 │ Haus2 │ Tuer │ 70 │
8 │ Haus1 │ Wand │ 30 │
9 │ Haus2 │ Fenster │ 60 │
──┴───────┴─────────┴────────┘
wird dann folgende Tabelle in Blatt2:
Tabelle:
F:\[SummeMitCollection.xls]!Tabelle2
│ A │ B │ C │
──┼───────┼─────────┼────────┤
1 │ Geb │ Teil │ Betrag │
2 │ Haus1 │ Fenster │ 50 │
3 │ │ Tuer │ 200 │
4 │ │ Wand │ 30 │
5 │ Haus2 │ Fenster │ 60 │
6 │ │ Tuer │ 170 │
7 │ Haus3 │ Tuer │ 100 │
──┴───────┴─────────┴────────┘
Tabellendarstellung erreicht mit dem Code in FAQ2363
wenn du nachfolgenden Code laufen läßt.
Getestet auf XL97 und XL2002.
PS: Auf Collection stiess ich nur per Zufall. Leider habe ich darüber nur das was in der Vba-Hilfe steht:frowning:
Und den Code dann durch reines Ausprobieren entwickelt.
Von daher ist mir noch ein wenig unklar warum er eigentlich funktioniert, genauer warum in die Zellen geschrieben wird.
Ich mutmaße es ist so wie wenn ich sage
Set Bereich = Range(„A1:E7“)
Bereich.cells(3,4)=„Hello world“)
da wird ja auch in D3 hineingeschrieben.
Wenn jmd. eine Inetadresse kennt wo man mehr zu Collections, mit Beispielen usw. nachlesen kann, immer her mit den Links
Gruß
Reinhard
Option Explicit
'
Sub Teilsummen()
' 2007 by Reinhard
Dim C As New Collection, Zei As Long, Ws1 As Worksheet, Letzte As Long
Set Ws1 = Worksheets("Tabelle1")
Letzte = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row
With Worksheets("Tabelle2")
.UsedRange.ClearContents
Ws1.Range("A1:C1").Copy Destination:=.Range("B1")
Ws1.Range("A2:C" & Letzte).Copy Destination:=.Range("B2")
.Range("A2:A" & Letzte).FormulaR1C1 = "=RC[1]&RC[2]"
.Range("A1:smiley:" & Letzte).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, \_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
On Error GoTo Dazu
For Zei = 2 To Letzte
C.Add Item:=.Cells(Zei, 4), key:=.Cells(Zei, 1)
Next Zei
On Error GoTo 0
For Zei = Letzte To 3 Step -1
If .Cells(Zei, 1) = .Cells(Zei - 1, 1) Then
.Cells(Zei, 1).EntireRow.Delete
ElseIf .Cells(Zei, 2) = .Cells(Zei - 1, 2) Then
.Cells(Zei, 2) = ""
End If
Next Zei
.Columns("A:A").Delete
Exit Sub
Dazu:
C(.Cells(Zei, 1)) = C(.Cells(Zei, 1)) + .Cells(Zei, 4)
Resume Next
End With
End Sub
Hi Phil
ich will ja nicht nerven aber hoffe du kannst mir trotzdem weiter helfen.
Ich habe mir Haus1 Haus2 … aus meiner tabelle ausgelesen und in eine combobox geladen kann sie auch ansprechen und mit value als string speichern nur geht es auch das ich mir dann in ein tabellenblatt z.b. nur haus1 oder haus2, welches ich mir in der combobox ausgesucht habe allein anzeigen.habe es versucht, komme nur nocht damit zurecht,da ja haus1 und haus2 … in 1 2 3 4 als long abgespeichert werden und ich nicht weis wie ich diese sorichtig ansprechen kann.
Vielen Dank
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hi
Also erst noch mal zum Verständnis:
ArrSort ist ein dreispaltiges Array, Mathematiker würden sagen eine dreidimensionale Matrix. Du kanns es dir vorstellen wie ein Koordinatensystem mit 3 Achsen (x,y,z(=Raumachse)).
Um einen Wert anzusprechen benötigt man 3 Parameter.
=> analog zum Beispiel: Um einen Punkt zeichnen zu können musst du wissen wo der x, y und z wert sein soll.
Die „Punkte“ die die Namen der Häser enthalten habe ich alle auf „die X-Achse“ gelegt. Also ArrSort(Zahl,0,0) enthält die Namen der Häuser.
Um Informationen zu einem Haus zu ermitteln geht man alle Haus-Namen durch und prüft, ob es das gewünschte ist:
For i = 0 to AnzahlDerHäuser
if ArrSort(i,0,0) = "" then exit for
if ArrSort(i,0,0) = GesuchtesHaus Then
'Informationen ermitteln
end if
Next i
Anzahl der Häuser erfährst du z.B. mit Ubound(ArrSort,1)
Die zweite Zeile verhindert, dass er noch leere Felder am Schluss durchsucht.
Die Informationen zu diesem Haus stehen jetzt alle in ArrSort( i ,y,z).
i ist also erst mal Fix.
Als GesuchtesHaus kannst du z.B. Combo1.Value übergeben.
Die Namen der Gegenstände können mit ArrSort(i, x, 0) (für x = 1 bis Ubound(ArrSort,2), solange ArrSort(i, x, 0) nicht „“ ist) ermittelt werden, die Preise dazu mit ArrSort(i ,x ,1)
(selber i und x wert = selber Gegenstand =>Name(0) und Preis(1)).
Hoffe das hilft dir!
Gruß Phil
Hi Phil
danke erst mal für die gute ausführliche erklärung, dadurch ist mir einiges klar geworden.
aber ich habe wieder mal eine kleines problem.
habe es jetzt soweit hinbekommen, das ich die häuser schön einlesen kann in eine combobox nur ich weis nicht so recht wie ich sie benutzen kann. wenn ich mir aus der combobox z.b. haus3 aussuche hab ich sie als variable nur ich möchte sie in den sheet komplett sichen und nur das haus3 ausgeben lassen. wollte einfach die HausNr ersetzen durch die variable von der combobox nur das geht ja nicht, weil ja die häuser in 1 bis … zählt und nicht als string.muß mann die häuser irgendwie durchzählen in der combobox und den wert dann nehmen oder wie kann man dies tun, das ich arrsort(x,y,z) nutzen kann?
Danke
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hi Phil
ich habe es mal etwas umgestellt un dbin wieder auf ein problem gestoßen.
die ausgangstabelle ist immer noch die gleiche, dann habe ich die häuser in senkrechter form einzelln mit dem makro eingelesen und die gegenstände in vertikaler form wie halt x und y, nur habe ich jetzt das problem das z der geldbetrag dem richtigen haus und richtigen gegenstand zuzuordnen.
im prinzip wie du es mir erklärt hast mit x y z. kannst du mir da bitte weiter helfen.
Danke
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo David,
Habe das jetzt mal erstellt und hochgeladen:
http://mitglied.lycos.de/rpgworld/Sortieren.xls
(Rechtsklick->Ziel speichern unter… sonst geht es gleich im Browser auf!)
Klicke auf Extras->Makro->Makros… da ist dann alles.
Kannst ja mal debuggen um es besser zu verstehen.
Gruß Phil
Hi
erst einmal vielen dank für die datei,
ich denke ich nerve langsam aber kannst du mir noch bei 2 problemen helfen.
das erste:kann man irgendwie die häuser sortieren haus1 2 3… weil diese durcheinander angezeigt werden
das zweite: habe die häuser jetzt waagerecht und die gegenstände senkrecht und möchte diese jedem haus zuordnen. so zum beispiel
Haus1 Haus2 Haus3 Haus4
Fenster 100 200
Tür 50 50 50
Treppe 25 25 25
Schalter 10 10 10
geht das irgendwie?
Vielen Dank
Gruß David
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Sorry das Beispiel war nicht richtig dargestellt
______________Haus1_____Haus2____Haus3____Haus4
Fenster_________100________________200______
Tür_____________50________50________________50
Treppe__________25________25________________25
Schalter_________10_________________10_______10
Schaut besser aus
Danke
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hallo David,
Sorry das Beispiel war nicht richtig dargestellt
Hier ist eine gute Lösung für so etwas: FAQ:2363
Hallo David,
das erste:kann man irgendwie die häuser sortieren haus1 2 3…
weil diese durcheinander angezeigt werden
wenn es also doch auch daruf hinaus läuft hättest du dir auch mal den Code von Reinhard ansehen können. (Wenigstens ein kurzes Feedback und Danke an ihn wäre vieleich auch nett gewesen…)
Mann könnte das mit dem Sortieren jetzt natürlich auch „von Hand“ machen, mit einem zweiten Array:
Die Anfangsbuchstaben miteinander vergleichen
(If Asc(LCase(Left(Wert1,1))) Sortieren…)
Sie ist folgendermaßen definiert:
Range(Cell1, [Cell2]).Sort([Key1], [Order1 As XlSortOrder = xlAscending], [Key2], [Type], [Order2 As XlSortOrder = xlAscending], [Key3], [Order3 As XlSortOrder = xlAscending], [Header As XlYesNoGuess = xlNo], [OrderCustom], [MatchCase], [Orientation As XlSortOrientation = xlSortRows], [SortMethod As XlSortMethod = xlPinYin])
In deinem Fall geht das dann folgendermaßen:
Sheets(2).Activate 'Unbedingt schon vor ".Sort" sonst wird ein Fehler ausgegeben.
'Die Häuser sortieren:
Sheets(2).Range("B1", Sheets(2).Cells(MaxRow2, lColumn)).Sort \_
Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, \_
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
'Die Gegenstände sortieren:
Sheets(2).Range("A2", Sheets(2).Cells(MaxRow2, lColumn)).Sort \_
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, \_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
das zweite: habe die häuser jetzt waagerecht und die
gegenstände senkrecht und möchte diese jedem haus zuordnen.
Hier musst du dann für die Spalten auch eine Variable einsetzen.
Außerdem musst du jedes mal prüfen ob der Gegenstand schon in einem anderen Haus vorkam und ihn, falls nicht, zur Liste hinzufügen:
lColumn = 2
MaxRow2 = 1
For HausNr = 1 To MaxRow
If ArrSort(HausNr, 0, 0) = "" Then Exit For
Sheets(2).Cells(1, lColumn) = ArrSort(HausNr, 0, 0)
For GegNr = 1 To MaxRow
If ArrSort(HausNr, GegNr, 0) = "" Then Exit For
For lRow = 2 To MaxRow2 + 1
If lRow = MaxRow2 + 1 Then 'Gegenstand zur Liste hinzufügen
Sheets(2).Cells(lRow, 1) = ArrSort(HausNr, GegNr, 0)
Sheets(2).Cells(lRow, lColumn) = ArrSort(HausNr, GegNr, 1)
MaxRow2 = MaxRow2 + 1
Exit For
End If
If Sheets(2).Cells(lRow, 1) = ArrSort(HausNr, GegNr, 0) Then
'Gegenstand ist schon in Liste -\> nur Wert eintragen
Sheets(2).Cells(lRow, lColumn) = ArrSort(HausNr, GegNr, 1)
Exit For
End If
Next lRow
Next GegNr
lColumn = lColumn + 1
Next HausNr
Kleiner Hinweis um Verwirrung zu vermeiden:
Im Code kommt dann natürlich erst die Antwort auf Frage 2 und dannach die zu Frage 1, also erst in das Sheet schreiben, dann sortieren.
Habe die Datei aktualisiert:
http://mitglied.lycos.de/rpgworld/Sortieren2.xls
(Rechtsklick->Ziel speichern unter… sonst geht es gleich im Browser auf!)
Gruß Phil
[MOD] Tippfehler korrigiert
Kleiner Hinweis um Verwirrung zu vermeiden:
Im Code kommt dann natürlich erst die Antwort auf Frage 1 und
dannach die zu Frage 2, also erst in das Sheet schreiben, dann
sortieren.
Verdammt, jetzt hab ichs auch noch falsch rum hingeschrieben, jetzt ist dat Chaos natürlich perfekt!
So gehört es:
Kleiner Hinweis um Verwirrung zu vermeiden:
Im Code kommt dann natürlich erst die Antwort auf Frage 2 und
dannach die zu Frage 1 , also erst in das Sheet schreiben, dann
sortieren.
So ein misst aber auch! Wieso muss mann den in diesem Forum erst zum Mod aufsteigen um edits machen zu können… Ist doch MEIN Beitrag!!
[MOD] Ist im Beitrag oben korrigiert
Hallo BadPhil,
Verdammt, jetzt hab ichs auch noch falsch rum hingeschrieben,
jetzt ist dat Chaos natürlich perfekt!So gehört es:
Kleiner Hinweis um Verwirrung zu vermeiden:
Im Code kommt dann natürlich erst die Antwort auf Frage
2 und
dannach die zu Frage 1 , also erst in das Sheet
schreiben, dann
sortieren.So ein misst aber auch! Wieso muss mann den in diesem Forum
erst zum Mod aufsteigen um edits machen zu können… Ist doch
MEIN Beitrag!!
ich weiß jetzt nicht ob ich dir was sage was du schon weißt oder nicht.
Eigene Beiträge kannst du zwar nicht editieren, aber kopieren, dann löschen, ändern, neu hochschicken.
Das Löschen geht nur solange kein Anderer darauf geantwortet hat.
Übrigens Danke, daß wenigstens du mich liest )
Gruß
Reinhard