Thema: Ereignis zeitweise mit Fehler

Hallo,
ich bräuchte mal Hilfe.
Ich habe in einem Tabellenblatt dieses Ereignis stehen:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$H$2:$H$307" Then
Dim H As Variant
End If
For Each H In Range("L2:L307")
If H.Value <> H.Offset(0, -4) Then
H.Offset(0, -4) = H.Value
End If
Next
If Target.Address = "$J$2:$J$307" Then
Dim N As Variant
End If
For Each N In Range("N2:N307")
If N.Value <> N.Offset(0, -4) Then
N.Offset(0, -4) = N.Value
End If
Next
End Sub

Damit werden Zahlen (0-9) automatisch übertragen.
Eigentlich läuft es, aber die 0 wird beim Eintippen sehr oft nicht übertragen, andere Ziffern sind immer fehlerfrei.
Ich gebe in der Spalte H eine Zahl, z.B. 3 ein, dann wird diese Zahl in die Spalte L übertragen.
Dann gebe ich in der Spalte J eine Zahl ein, z.B. 2, dann wird diese Zahl in die Spalte N übertragen.
In den Spalten L und N existiert eine Funktion (Sverweis). Gibt es dadurch Probleme?
Kann man die Prozedur evtl. umschreiben, so dass sie stabiler wird und dieser Fehler nicht auftritt?
Hilfe wäre nett.
Schönen Gruß
Burghard

Hallo Burghard,
Deine Funktion funktioniert nicht so, wie Du denkst, sondern völlig anders. Der Code überträgt die Einträge aus Spalte L in Spalte H und aus Spalte N in Spalte J und nicht umgekehrt.
Sinnvoll wäre es, wenn Du das relevante Tabellenblatt hochladen könntest, vielleicht kann man dann das Problem erkennen.
Viele Grüße
Björn

Hallo Burghard,
vielleicht passt diese Change-Ereignisprozedur besser:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range  ' überträgt Änderungen von Sp.H in Sp.L und von Sp.J in Sp.N
If Not Intersect(Target, Range("H2:H307,J2:J307")) Is Nothing Then
Application.EnableEvents = False
For Each rng In Intersect(Target, Range("H2:H307,J2:J307"))
If rng.Offset(0, 4).Text <> rng.Text Then rng.Offset(0, 4) = rng
Next rng
Application.EnableEvents = True
End If
End Sub

Bevor du das testest, solltest du die Worksheet_SelectionChange-Prozedur stilllegen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
vielen Dank für die schnelle Hilfe. Die Prozedur funktioniert. Es gibt nur noch ein kleines Problem.
Da ich in den Spalten L und N eine Index-Funktion habe, brauche ich quasi noch eine Rückübertragung von L nach H und von N nach J.
Das hängt damit zusammen, dass ich, wie in der Spalte Q erkennbar, die Punktewertung für diese Mannschaft vornehme.
Ich habe eine kleine Grafik hochgeladen, die das Problem zeigt (die Übertragung der Zahlen erfolgte mittels Deiner Prozedur).

Falls nötig könnte ich auch die gesamte Datei hochladen. Die Datei ist allerdings etwas größer und ich habe nur eine langsame Modemverbindung (kein DSL).
Schönen Gruß
Burghard

Hallo Burghard,
letzteres war schon gestern mein Vorschlag. Mehr als anbieten will ich meine Hilfe aber auch nicht.
Viel Glück
Björn

Hallo Björn,
vielen Dank für den Hinweis. Ich habe die Datei hochgeladen.
https://www.herber.de/bbs/user/48713.zip
Bei der Datei ist mir noch ein Problem aufgefallen.
Wenn ich in der Spalte C den Autofilter beispielsweise auf "VfB Stuttgart" setzen, dann funktioniert das, aber wie in Spalte K anhand der Daten ersichtlich, gibt es die Zahlen in Spalte K doppelt.
Kann man irgendwie so filtern, dass ich mittels einer Inputbox den Namen (z.B. VfB Stuttgart) eingebe und in Kombination bei den doppelten Datensätzen lt. Zahl in Spalte K jeweils das zweite Vorkommen ausgeblendet wird. Ich weiß, dass ist schwierig (unmöglich?).
Schönen Gruß
Burghard

Hallo Burghard,
kannst Du mal versuchen, zu beschreiben, was Du mit Deiner Datei eigentlich abbilden willst?
Wenn ich das Ziel verstehe, fällt es leichter zu helfen.
Ad hoc fallen mir z. B. folgende Fragen ein:
Wo sollen die Tipps eingegeben werden?
Wo sollen die tatsächlichen Ergebnisse eingegeben werden?
Warum steht in Spalte B und K das Gleiche?
Warum soll auch in den Spalten H und L sowie J und N das Gleiche stehen?
Viele Grüße
Björn

Hallo Björn,
ich habe Dir einmal eine Erklärung für die Arbeitsmappe hochgeladen.
https://www.herber.de/bbs/user/48724.doc
Schönen Gruß
Burghard

Hallo Burghard,
versuch's mal mit folgendem Code anstelle des bisher verwendeten:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range  ' überträgt Änderungen von Sp.H in Sp.L und von Sp.J in Sp.N
If Not Intersect(Target, Range("H2:H307,J2:J307")) Is Nothing Then
Application.EnableEvents = False
For Each rng In ThisWorkbook.ActiveSheet.Range("L2:L307")
If rng.Offset(0, -4).Value <> rng.Value Then rng.Offset(0, -4).Value = rng.Value
Next rng
For Each rng In ThisWorkbook.ActiveSheet.Range("N2:N307")
If rng.Offset(0, -4).Value <> rng.Value Then rng.Offset(0, -4).Value = rng.Value
Next rng
Application.EnableEvents = True
End If
End Sub

Das löst zwar noch nicht Dein Problem mit dem Autofilter, aber das andere Problem sollte es beheben.
Viele Grüße
Björn

Nochmals hallo Burghard,
'ne perfekte Idee für das Autofilterproblem habe ich nicht, aber wie wäre es mit folgendem Workaraound:
In Spalte Q anstatt jeweils 17 Zellen zu verbinden schreibst du in jede der 17 Zellen die gleiche Formel (z.B. =$R$2 beim VfB Stuttgart)hinein. Dann formatierst Du die Schrift in den ersten 8 und den letzten 8 Zellen weiß.
Als nächstes markierst Du alle 17 Zellen und passt den Rahmen (Rahmenfarbe: automatisch) so an, dass er nur außen um den markierten Bereich geht. Bevor Du OK drückst wechselst Du die Rahmenfarbe auf Weiß und fügst in die Mitte eine waagerechte Rahmenlinie ein. Nun die Änderungen mit OK bestätigen.
Das Look and Feel ist genauso wie vorher, nur mit dem Unterschied, dass Du nun den Autofilter auf Spalte Q legen kannst, um die 17 Partien einer Mannschaft zu sehen.
Viele Grüße und guten Rutsch
Björn

Hi Björn und Burghard,
dass diese Prozedur ein Problem behebt, bezweifle ich.
Die Prozedur überschreibt bei jeder Eingabe in den Spalten H oder J
die Inhalte dieser beiden Spalten durch die Ihnalte von L und N.
Insbesondere auch der eingegebene Wert ist dann futsch...
Hier eine Mappe zum Testen. Was passiert, wenn man in H3 etwas eingibt?
https://www.herber.de/bbs/user/48728.xls
Noch eins:
Da Worksheet_Change im Modul der Tabelle steht, ist "ThisWorkbook.ActiveSheet." überflüssig.
Stattdessen könnte man "Me." schreiben - oder es ganz weglassen, so wie in der Zeile
If Not Intersect(Target, Range("H2:H307,J2:J307")) Is Nothing Then
Zum Gesamtproblem:
Burghard, auch deine umfangreiche Erklärung im Worddokument hat für mich nicht geklärt,
was genau bei einer Eingabe in eine Zelle in Spalte H passieren soll.
Besser als die schon hochgeladene Grafik wäre, die Excelmappe hochzuladen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
und falls wir nicht mehr rechtzeitig voneinander hören: Guten Rutsch ins Jahr 2008!

Hallo Erich,
Deine Zweifel in allen Ehren, aber ich denke Du liegst falsch.
Von der grundsätzlichen Funktionsweise des Makros her hast Du natürlich recht, aber in Kombination mit dem SVerweis überschreibt es eben gerade nicht die getätigten Eingaben, wie Du hier https://www.herber.de/bbs/user/48730.zip sehen kannst.
Betreffend "ThisWorkbook.ActiveSheet." hast Du sicherlich recht. Ich bin immer froh, wenn der Code funktioniert, optimieren müssen ihn andere, dafür stecke ich nicht tief genug drin.
Viele Grüße
und guten Rutsch
Björn

Hi Björn,
ein wenig liegen wir wohl beide richtig bzw. falsch.
Wenn etwas in J14 eingegeben wird,
wird der Wert von J14 per SVERWEIS in N14 und N31 (da stehen die selben Formeln drin) übertragen.
Die Prozedur überträgt alle L- und N-Werte nach H und J,
dabei wird J14 mit seinem Wert überschrieben.
Wenn nun etwas in J31 eingegeben wird,
wird der Wert von J14 NICHT übertragen - die beiden (identischen) SVERWEISe bedienen sich aus Zeile 14.
N31 bleibt gleich J14.
Die Prozedur überträgt alle L- und N-Werte nach H und J,
dabei wird der gerade eingegebene Wert in J31 überklatscht mit dem Wert von N31, und der ist gleich J14.
Der Benutzer müsste also aufpassen, dass er Änderungen immer nur in der Zeile vornimmt,
in der eine Paarung erstmals auftritt.
Rückmeldung wäre nett! - Erich aus Kamp-Lintfort wünscht ein frohes Neues!

Hallo zusammen,
eigentlich geht es doch nur darum, dass bei Eingabe in H oder J der eingegebene Wert in die andere Zeile
mit der selben Paarungsnummer übertragen wird.
Die Spalten L und N interessieren hier nicht, die Formeln erledigen die Übertragung dorthin.
Probiert mal

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range        ' kopiert Änderungen in Sp.H und J in die Zeile mit gleicher Nr.
If Intersect(Target, Range("H2:H307,J2:J307")) Is Nothing Then Exit Sub
Set rng = Columns(2).Find(What:=Cells(Target.Row, 2), _
After:=Cells(Target.Row, 2), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng.Row = Target.Row Then
MsgBox "Spielpaarung " & Cells(Target.Row, 2) & " gibts nur einmal"
Else
Application.EnableEvents = False
Cells(rng.Row, Target.Column) = Target.Value
Application.EnableEvents = True
End If
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
vielen Dank für Deine Nachricht. Ich bewundere Deine Geduld.
Leider führt Deine Programmierung zu einer Fehlermeldung (Debugging) im Abschnitt:
Set rng = Columns(2).Find(What:=Cells(Target.Row, 2), _
After:=Cells(Target.Row, 2), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Kannst Du vielleicht doch noch einmal schauen, ob sich bei meiner Programmierung (siehe unten) nicht eine Sache verbessern läßt?
Bei Eingabe in Spalte H oder J funktionieren bei der unteren Prozedur alle Ziffern 1 bis 9. Dann ist alles in Ordnung. Es gibt nur ein Problem mit der 0. Wenn ich aber statt 0 eingebe 0,1 , dann funktioniert auch die Sache mit der 0 einwandfrei (angezeigt wird die Ganzzahl 0 in den Spalten H,J,L,N).
Ich bräuchte in der Ereignisprozedur nur 0.1 an die in Spalte H oder J eingegebene Zahl anhängen. Dann funktioniert die Sache auch mit der 0.
Allerdings gibt es eine Fehlermeldung (Debugging), wenn ich z.B. für eine Änderung die Zahleneingabe in Spalte H oder J wieder lösche.
Frage:
Kann man die untere Prozedur etwas umgestalten, dass die Fehlermeldung (Debugging) nicht auftritt, wenn ich die Zahleneingabe in Spalte H oder J ändere/lösche?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Worksheets("Auswertung").Unprotect
If Target.Address = "$H$2:$H$307" Then
Dim H As Variant
End If
For Each H In Range("L2:L307")
If H.Offset(0, -4) <> H.Value Then
H.Offset(0, -4) = H.Value + 0.1
End If
Next
If Target.Address = "$J$2:$J$307" Then
Dim N As Variant
End If
For Each N In Range("N2:N307")
If N.Offset(0, -4) <> N.Value Then
N.Offset(0, -4) = N.Value + 0.1
End If
Next
End Sub

Das Filterproblem konnte ich lösen. Ich habe in der Spalte K mit dem "Autofilter" über "benutzerdefiniert" den Filter auf kleiner als 154 gesetzt. Dann werden die Datensätze richtig gefiltert.
Erich, ich denke, wir sollten den Thread beenden, wenn Du meinst, dass die o.g. Änderung in meiner Programmierung nicht machbar ist ohne eine Fehlermeldung (Anhängen des Wertes 0,1).
Ich danke Dir für Deine Geduld und Ausdauer!
Schönen Gruß
Burghard

Hallo Burghard,
nach wie vor halte ich es für überflüssig, für das Kopieren einer Eingabe in H oder J
die Spalten L oder N zu verwenden.
Ist nicht das Ziel der Prozedur, dass die Einträge in den Spalten H und J
bei Paarungen mit gleicher Nr. identisch sind? (Erbitte Antwort hierzu!)
Der Fehler (die genaue Angabe des Fehlerhinweises wäre praktisch gewesen!) tritt auf,
weil in deiner Excelversion der Find-Befehl den Parameter "SearchFormat" noch nicht kennt.
Lasse ihn weg, dann sieht der Befehl so aus:

Set rng = Columns(2).Find(What:=Cells(Target.Row, 2), _
After:=Cells(Target.Row, 2), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

Ein Laufzeitfehler 91 - "Objektvariable oder With-Blockvariable nicht festgelegt"
in Zeile "If rng.Row = Target.Row Then" tritt auf, wenn Spalte 2 (B) ausgeblendet ist.
Wenn du Spalte B einblendest, funzt die Prozedur.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hi nochmal,
wenn Spalte B ausgeblendet bleiben soll, geht das so:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range        ' kopiert Änderungen in Sp.H und J in die Zeile mit gleicher Nr.
If Intersect(Target, Range("H2:H307,J2:J307")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Columns(2).Hidden = False
Set rng = Columns(2).Find(What:=Cells(Target.Row, 2), _
After:=Cells(Target.Row, 2), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If rng.Row = Target.Row Then
MsgBox "Spielpaarung " & Cells(Target.Row, 2) & " gibts nur einmal"
Else
Application.EnableEvents = False
Cells(rng.Row, Target.Column) = Target.Value
Application.EnableEvents = True
End If
Columns(2).Hidden = True
Application.ScreenUpdating = True
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
vielen Dank für Deine unendliche Geduld und viele Arbeit.
Jetzt kommt das Beste: Es funktioniert einwandfrei! Einfach super! Genauso, wie ich es haben wollte.
Danke!, danke, danke!
Toll!
Schönen Gruß
Burghard

Hallo Erich, hallo Burghard,
bin familiär bedingt erst jetzt wieder online.
Wie ich sehe habt ihr, bzw. Du Erich, das Problem nun endgültig gelöst - prima.
Viele Grüße und ein gutes 2008
Björn
P.S.: Ach ja, Erich, Du hattest natürlich Recht mit Deinem Kommentar zu meinem letzten Vorschlag - ein bisschen falsch lagen wir wohl beide :-).

Hallo Erich,
vielen Dank für die Hinweise. Du hast Recht. Mittlerweise bin ich schon ganz konfus.
Wenn ich die Ereignisprozedur aus dem Tabellenblatt "Auswertung" rausnehme, läuft die Sache auch fast vollständig.
Zu Deiner Frage:
Bitte mach das einmal in meiner hochgeladenen Fussballdatei:
Wenn Du in H14 z.B. 2 einträgst, erscheint in L14 ebenfalls diese Zahl und ebenfalls in L31.
Wenn Du in J14 z.B. 1 einträgst, erscheint in N14 ebenfalls diese Zahl und ebenfalls in N31.
Der Umweg über den SVERWEIS in den Spalten L und N sucht mir das zweite Vorkommen der Spielpaarung (zum Zwecke der Punktewertung in Spalte P).
Ich bekomme in Spalte P31 keine Auswertung, weil mir in H31 der Wert von H14 fehlt (oder der von L31) und in J31 der Wert von J14 (oder der von N31).
Ich brauche die Zahlen in den Spalten H und J, um die Punktewertung in Spalte P zu erhalten.
Das Thema schafft mich. Ich hoffe, ich mache Dir nicht zuviel Arbeit. Sorry!
Auch Dir einen guten Rutsch und insgesamt ein gutes neues Jahr.
Vielen Dank für all Deine bisherigen Bemühungen!
Schönen Gruß
Burghard

Hallo Burghard,
hast Du meine Antwort von heute 16:48:30 Uhr überhaupt gelesen?
Ich behaupte mal, die Datei macht das was Du willst.
Viele Grüße
Björn

Hallo Björn,
vielen Dank für die guten Wünsche. Ich hoffe, auch Du hast den Jahresübergang gut überstanden.
Auch Dir ein gutes Jahr 2008!
Zu meinem Problem:
Leider löst Dein Programmiervorschlag die Sache nicht (siehe auch Hinweis von Erich). Schade.
Könnte es sein, dass meine Excel-Version "(Excel-Version) evtl. anders "denkt" als Deine"
Beschreibung:
Wenn ich in das Tabellenblatt "Auswertung" die nachstehende Prozedur einfüge, funktioniert die Sache komplett , allerdings nur mit den Ziffern 1 bis 9.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$H$2:$H$307" Then
Dim H As Variant
End If
For Each H In Range("L2:L307")
If H.Offset(0, -4) <> H.Value Then
H.Offset(0, -4) = H.Value
End If
Next
If Target.Address = "$J$2:$J$307" Then
Dim N As Variant
End If
For Each N In Range("N2:N307")
If N.Offset(0, -4) <> N.Value Then
N.Offset(0, -4) = N.Value
End If
Next
End Sub

Beispiel:
Gebe ich in H 14 die 2 ein, dann erscheint in H31 und L14 und L31 auch die 2.
Gebe ich in J 14 die 1 ein, dann erscheint in J31 und L31 und N31 auch die 2.
Alles wie gewünscht.
Wenn ich die 2 und die 1 mit einer 0 überschreiben, geht auch alles.
Lösche ich aber in H14 und in J14 die Zahlen und trage danach eine 0 in H14 und in J14 ein, dann erscheint weder in H31 noch in J31 die 0.
Wenn ich aber nach der Löschung der Zahlen in H14 und J14 anschließend in H14 und in J14 den Wert 0,1 eintrage, läuft alles wieder. Irgendwie hat Excel ein Problem mit der Zahl, dem Wert 0.
Idee:
Kann ich zu dem Wert der eingetragen wird, automatisch 0,1 addieren? Dann würde alles laufen. Angezeigt wird nur der Ganzwert. Das reicht mir.
Ich habe einmal die Programmierung geändert. Das führt aber bei einer zweiten Veränderung der Zelle (Löschen, Anklicken) zu einer Fehlermeldung. Könnte man die umgehen?
Beispiel für eine gedachte Änderung:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$H$2:$H$307" Then
Dim H As Variant
End If
For Each H In Range("L2:L307")
If H.Offset(0, -4) <> H.Value Then
H.Offset(0, -4) = H.Value + 0.1
End If
Next
If Target.Address = "$J$2:$J$307" Then
Dim N As Variant
End If
For Each N In Range("N2:N307")
If N.Offset(0, -4) <> N.Value Then
N.Offset(0, -4) = N.Value + 0.1
End If
Next
End Sub

Ich hoffe, ich nerve Dich nicht zu sehr.
Die Sache mit dem Filtern oder Ausblenden, muss ich mir noch einmal gründlich anschauen. Vielen Dank für Deine Vorschläge.
Wenn die Änderung der o.g. Prozedur (addieren von 0,1 ohne Fehlermeldung nicht geht) sollte man m.E. den Thread beenden.
Vielen Dank für alle Deine Bemühungen!
Schönen Gruß
Burghard

Hallo Burghard,
das mit der Rückübertragung verstehe ich nicht.
Wenn du in H5 etwas eingibst, wird der Wert durch Worksheet_Change auf L5 übertragen.
Eine evtl. vorher in L5 stehende (INDEX-)Formel wird dabei überschrieben - sie ist weg.
Die Inhalte von H5 und L5 sind identisch. Was soll von L nach H "rückübertragen" werden?
Ich habe den Verdacht, dass die Struktur deiner Tabelle die Probleme erst erzeugt.
Wie Björn schon schrieb:
Sinnvoll wäre es, wenn Du das relevante Tabellenblatt hochladen könntest,
vielleicht kann man dann das Problem erkennen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
ich habe Dir einmal eine Erklärung für die Arbeitsmappe hochgeladen.
https://www.herber.de/bbs/user/48724.doc
Schönen Gruß
Burghard