Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1512to1516
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Wenn Werte identisch, dann Differenz.

Wenn Werte identisch, dann Differenz.
17.09.2016 22:20:06
Taipan

Hallo zusammen,
ich versuche gerade ein Makro zu programmieren für den Fall, dass in Tabelle1 mehrere Nummern mit Monat und Wert stehen. In einem zweiten Tabellenblatt (Tabelle2) stehen ebenfalls Nummern mit Monat und Wert. Sofern Nummer und Datum in beiden Blättern identisch sind soll der Wert aus Tabelle1 um den Wert aus Tabelle2 reduziert werden.
Folgende Tabelle als Beispiel
https://www.herber.de/bbs/user/108238.xlsx
Ich habe versucht das über Zwischenwerte zu lösen. Mir war schon klar, dass das evtl. nicht klappt. Nur nun bin ich so erstaunt, weil ich gar nicht nachvollziehen kann, wie mein Makro zu seinen Aktionen kommt. Es werden nun Spalten geleert, das kann ich anhand des Codes überhaupt nicht nachvollziehen.
Habe es dazu geschrieben, wann welche Spalte gelöscht wird.
Würde mich erstmal freuen, wenn mir jemand erklären kann wieso es zu dem Vorgang kommt. Und ansonsten natürlich auch sehr, wenn jemand weiß, wie ich das Problem lösen kann :)
Anbei der Code:

Sub Test()
Dim Monat1 As Range
Dim Nr1   As Range
Dim Wert1 As Range
Dim Monat2 As Range
Dim Nr2 As Range
Dim Wert2 As Range
lr1 = Worksheets("Tabelle1").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Worksheets("Tabelle2").Cells(Rows.Count, "A").End(xlUp).Row
Set Monat1 = Worksheets("Tabelle1").Columns("C:C")
Set Nr1 = Worksheets("Tabelle1").Columns("A:A")
Set Monat2 = Worksheets("Tabelle2").Columns("B:B")
Set Nr2 = Worksheets("Tabelle2").Columns("A:A")
Set Wert2 = Worksheets("Tabelle2").Columns("C:C")
Set Wert1 = Worksheets("Tabelle1").Columns("D:D")
For i = 2 To lr1
Monat1(i).Value = Monat  'hier löscht er Spalte D, Tabelle1
Nr1(i).Value = Nr      'hier löscht er Spalte B, Tabelle1
Wert1(i).Value = Wert
For j = 2 To lr2
If Monat2(j).Value = Monat And Nr2(j).Value = Nr Then
Wert = Wert - Wert2(j).Value
End If
Next j
Wert1(i).Value = Wert
Next i
End Sub

Danke und Grüße
Taipan

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Warum bist du denn so erstaunt, ...
18.09.2016 02:40:45
Luc:-?
…Taipan,
du weist doch genau das in deinem Pgm an?! Du weist den Zellen die Variablen Monat, Nr, Wert zu, die du weder deklariert noch mit irgendwelchen Werten gefüllt hast. Folglich sind sie leer. Hättest du Option Explicit an den Modul­Anfang geschrieben (oder besser in den VBE-Einstellungen Deklaration erforderlich angehakt), hätte dich der VBE darauf aufmerksam gemacht.
Übrigens kann man das sicher auch mit Fmln lösen; da würdest du auch mehr Unter­stützung bekommen.
Gruß, Luc :-?
Besser informiert mit …
Anzeige
AW: Warum bist du denn so erstaunt, ...
18.09.2016 10:48:39
Taipan
Hallo Luc,
danke für die schnelle Antwort. Ich verstehe nur nicht wieso er genau auf Spalte B und D kommt, weil ich die Bereiche ja in Splate C und A definiert habe.
Na gut, nun versuch eher mal eine Lösung zu finden, also mit Formeln habe ich das nur über eine Hilfsspalte geschafft. Ich habe in Tabelle 1 in Spalte E dann folgende Formel
{=WENN(ISTFEHLER(INDEX(Tabelle2!$A$2:$C$13;VERGLEICH(Tabelle1!A2&Tabelle1!C2; Tabelle2!$A$2:$A$13&Tabelle2!$B$2:$B$13;0);3));D2;D2-INDEX(Tabelle2!$A$2:$C$13; VERGLEICH(Tabelle1!A2&Tabelle1!C2;Tabelle2!$A$2:$A$13&Tabelle2!$B$2:$B$13;0);3)) }
Wenn ich die runterziehe funktioniert es auch, dass er mir die Werte abzieht, sofern diese vorhanden sind. Hast du eine Idee, wie ich das ohne zusätzliche Spalte lösen kann?
Danke und viele Grüße
Taipan
Anzeige
Deine 'Columns-Angabe' entspricht nicht der ...
18.09.2016 14:50:56
Luc:-?
…VBA-Syntax, Taipan,
das hatte ich übersehen und ggf liegt's daran. Überprüfe mal, was .Columns("C:C") anzeigt. Normalerweise wird die Spalte nämlich numerisch angegeben (evtl auch ein einzelner SpaltenBuchstabe, also "C" → mache ich nie!). Deine Angabe ist eine Range-Angabe → Range("C:C") darf man schreiben.
Fml-Ideen ggf später, habe gerade wenig Zeit…
Luc :-?
AW: Deine 'Columns-Angabe' entspricht nicht der ...
18.09.2016 17:33:19
Taipan
Oh ja, der Syntax :o Meine Vba-Kenntnisse sind wie angegeben noch recht bescheiden. Da muss ich mich definitiv noch mehr mit vertraut machen. Ich habe nun nochmal weiter an dem Makro gearbeitet.
Es funktioniert auch...fast.
Das Problem ist nur noch, dass ich den Monat als Text angegeben habe und es dann immer zum Fehler "Typen unverträglich" kommt (habe zum Test mal das in Zahl-Datum umgewandelt dann geht's). Aber auch, wenn ich Datum nicht als Double sondern String definiert ist auch der Fehler dort. Als was muss ich es definierten, damit er auch Text vergleicht?
Hier der Code:
Sub Test()
Dim Monat1 As Range
Dim Nr1   As Range
Dim Wert1 As Range
Dim Monat2 As Range
Dim Nr2 As Range
Dim Wert2 As Range
Dim Monat As Double
Dim Wert As Double
Dim Nr As Double
lr1 = Worksheets("Tabelle1").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Worksheets("Tabelle2").Cells(Rows.Count, "A").End(xlUp).Row
Set Monat1 = Worksheets("Tabelle1").Columns("C")
Set Nr1 = Worksheets("Tabelle1").Columns("A")
Set Monat2 = Worksheets("Tabelle2").Columns("B")
Set Nr2 = Worksheets("Tabelle2").Columns("A")
Set Wert2 = Worksheets("Tabelle2").Columns("C")
Set Wert1 = Worksheets("Tabelle1").Columns("D")
For i = 2 To 12
Wert = 0: Monat = 0: Nr = 0
Monat = Monat + Monat1.Cells(i, 1).Value  'hier löscht er Spalte D, Tabelle1
Nr = Nr + Nr1.Cells(i, 1).Value     'hier löscht er Spalte B, Tabelle1
Wert = Wert + Wert1.Cells(i, 1).Value
For j = 2 To lr2
If Monat2.Cells(j, 1).Value = Monat And Nr2.Cells(j, 1).Value = Nr Then
Wert = Wert - Wert2.Cells(j, 1).Value
End If
Next j
Wert1.Cells(i, 1).Value = Wert
Next i
End Sub

Die Range-angaben mit der i-ten Spalte war wohl auch wieder Syntax-Fehler, ich habe jetzt das .Cells dazwischen gesetzt. Nun klappt es. Bei der Definition der verschiedenen Ranges habe ich nun nur noch "A" geschrieben und nicht mehr "A:A". Für mich ist es leichter zu erkennen, wenn der Spaltenname in der Defiiniton der Range steht. Warum ist es empfehlenswert nur die entsprechende Zahl der Spalte anzugeben? :)
Danke erneut für deine Hilfe!!
Taipan
Anzeige
Besser so
18.09.2016 18:53:00
Michael
Hi,
hier mal ein optimierter Code mit "Dictionary", nachzulesen u.a. hier:
http://www.snb-vba.eu/VBA_Dictionary_en.html
Deine Tabelle2 enthält zwei Werte, die in Tabelle1 nicht vorkommen; weiterhin hast Du dort EINMAL "Juni " (Leerzeichen am Ende) statt "Juni" geschrieben - das könnte man mit trim() "erschlagen".
Der Code:

Option Explicit
Sub Neu()
Dim oD As Object
Dim lr&, z& ' & = as long
Dim ab, d1
With Worksheets("Tabelle1")
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
ab = .Range("A2:C" & lr)
d1 = .Range("D2:D" & lr)
End With
Set oD = CreateObject("scripting.dictionary")
For lr = 1 To UBound(ab): oD(ab(lr, 1) & "|" & ab(lr, 3)) = lr: Next
With Worksheets("Tabelle2")
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
ab = .Range("A2:C" & lr)
End With
For lr = 1 To UBound(ab)
z = oD(ab(lr, 1) & "|" & ab(lr, 2))
If z > 0 Then d1(z, 1) = d1(z, 1) - ab(lr, 3)
Next
Worksheets("Tabelle1").Range("D2").Resize(UBound(d1)) = d1
End Sub

Funktioniert prinzipiell so:
a) kopiere "alle" Daten (nur Nr. + Monat) aus Tabelle1 in das Dictionary (merke Dir die Zeilen-Nummer) und kopiere die Spalte D ins Array d1
b) kopiere alle Daten aus Tabelle2 ins Array ab
c) gehe das Array ab durch, und wenn Nr. + Monat übereinstimmen, ziehe den Wert vom Array d1 ab
d) schreibe das Array d zurück ins Blatt
Das flutscht auch bei x-1000 Daten.
Die Datei: https://www.herber.de/bbs/user/108244.xlsm
Schöne Grüße,
Michael
Anzeige
Vielen Dank!!
18.09.2016 20:09:29
Taipan
Klasse, das ist zwar ganz anders als vorher, aber funktioniert großartig.
Danke vor allem auch für die Erklärungen (+Link), so hatte ich auch eine Chance es zu verstehen. ;) Werde mich bestimmt nochmal intensiver mit dem Dictionary befassen, da es sich ja wohl sehr lohnt, wenn man größere Datenmengen hat. Ich kannte es bisher noch gar nicht, danke für den Code!
Oh das mit dem Leerzeichen dahinter ist auch ein guter Hinweis, den trim()-Befehl habe ich auch schon nachgelesen, damit bekomme ich die raus. Super! Danke für deine Zeit und Mühe! :)
DANKE und viele Grüße
Taipan
Anzeige
freut mich, gerne

341 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige