Office: VBA Zelle kopieren

Hi,

Wie kopiert man mittels VBA den Zelleninhalt in eine andere Zelle?

In einer Zelle (AE16) befindet sich eine Formel, welche einen Wert berechnet. Dieser Wert soll automatisch in eine andere Zelle (U8) kopiert/verlinkt werden - jedoch soll man auch den Wert in U8 manuell per Hand eintragen können.

Ich hab folgenden Code hier im Forum gefunden (schon angepasst):

Code:

If Target.Address = "$AE$16" Then Sheets("Tabelle1").Range("U8") = Target

Leider funktioniert das nicht bzw. weiß ich auch nicht an welcher Stelle ich dies im MakroEditor einfügen muss, da ich schon ein Makro nutze. Ich möchte später gern weitere Zellen miteinander verlinken, wobei kein Zirkelbezug entsteht.

So schaut ein Teil des bisherigen Makros aus:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
With Target
    If .Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("E8:X11")) Is Nothing Then
        x = Int(.Column / 5) - .Row + 7
        .Offset(x, -5 * x + 2 * (2 * (.Column Mod 5 = 3) + 1)) = Target
    End If
End With
Application.EnableEvents = True
End Sub

Kann mir jmd helfen? Ist sicherlich keine große Sache nur kenn ich mich mit VBA nicht aus. Danke

 

möchtest Du den wert übernehmen Range("A1")=Range("B1") oder die Formel Range("A1").copy=Range("B1") ?

Gruß Hajo

 

Ich möchte nur den berechneten Wert übernehmen, sobald er berechnet wurde.

 

man könnte daraus vermuten es ist eine Formel in der Zelle?

Da ist change das falsche Ereignis.
Da ist Private Sub Worksheet_Calculate()
das richtige Ereignis, das ist mir aber zu aufwendig.

Gruß Hajo

 

Genau.
Wie Eingangs geschrieben:

 

Wie bitte? Hab ich jetzt irgendwo was überlesen? Ansonsten ist das ja jetzt echt mal der klassische 3-Zeiler...

Code:

Private Sub Worksheet_Calculate()

    Worksheets("ziel").Range("u8").Value = Worksheets("quelle").Range("ae16").Value

End Sub

Gruß, Michael

 

Danke für die Hilfe, hab es vesucht an anzupassen, leider funktioniert dies nicht.

hier mein VBA Code:

Code:

Private Sub Worksheet_Calculate()

    Worksheets("Tabelle1").Range("u8").Value = Worksheets("Tabelle1").Range("ae16").Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Dim x As Integer
With Target
    If .Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("E8:X11")) Is Nothing Then
        x = Int(.Column / 5) - .Row + 7
        .Offset(x, -5 * x + 2 * (2 * (.Column Mod 5 = 3) + 1)) = Target
    End If
End With
Application.EnableEvents = True
End Sub

2 Fehler gab es:
Der Wert von AE16 wurde nicht in die Zelle U8 geschrieben/übernommen und falls man manuell einen Wert in U8 geschrieben hat ist Excel abgestürzt.

Auch als ich Worksheets("Tabelle1"). entfernt habe hat es noch nicht funktioniert.

edit: wenn ich den vba teil mit Private Sub Worksheet_Change(ByVal Target As Range) auskommentiere kommt eine Fehlermeldung und Excel wird geschlossen.

wenn ich Range("AE16").Value = Range("U8").Value schreibe und einen Wert in U8 eintrage wird dieser in die Zelle AE16 geschrieben. Schreibe ich jedoch Range("U8").Value = Range("AE16").Value (was ich eigentlich benötige) stürzt Excel entweder ab oder übernimmt nicht den wert aus AE16.

 

Hier ist in jedem Fall schonmal das obere "end sub" zu viel.

Für das Übernehmen des Wertes ist die "obere" Calculate-Routine zuständig. Die wird ausgelöst, wenn (u.a.) die Formel in AE16 einen neuen Wert berechnet. Diese Routine muß im Code des Blatts stehen, auf dem sich die Formel in AE16 befindet. (Wenn ein anderes Blatt berechnet wird, muß ja hier nichts kopiert werden...) Keine Neuberechnung --> keine Übernahme... Ist das automatische Berechnen in den Excel-Optionen ausgeschaltet? Passiert was bei Drücken von F9?

Ich hatte mir jetzt nicht genau angesehen, was die "untere" Change-Routine so genau macht. Aber da der Bereich E8:X11 überwacht wird und bei Änderung dort irgendwas passiert, könnte das vielleicht eine Endlos-Schleife mit dem Calculate-Ereignis auslösen. Es könnte helfen:

Code:

application.enableevents = false
Worksheets("Tabelle1").Range("u8").Value = Worksheets("Tabelle1").Range("ae16").Value 
application.enableevents = true

Damit wird durch die Übernahme des Wertes auf U8 nicht gleich das nächste Change-Ereignis ausgelöst, was ggf. das Calculate auslöst, was wiederum ...

Zirkelbezüge in Formeln werden von Excel schon zu Recht angemeckert. In VBA erkennt Excel diese Zirkel nicht...

Gruß, Michael

 

mit

Code:

application.enableevents = false
Range("u8").Value = Range("ae16").Value
application.enableevents = true

funktioniert das übertragen des Wertes von Zelle AE16 in U8 :-D jedoch funktioniert der andere VBA Code Teil nicht mehr :cry: .

Der andere VBA Teil funktioniert nur, wenn ich einen Wert manuell in U8 schreibe und nicht wenn dieser von AE16 übernommen wird.

edit:
Ich hab mal eine Bild hinzugefügt zur Verdeutlichung.
Im unteren Bereich werden die einzelnen Satzergebnisse eingetragen und am Ende der Zeile wird dann das Ergebnis 3:1, 3:0 oder so berechnet.
Diese Ergebnis soll dann automatisch in die obere Tabelle übernommen werden.
Wenn ich ein Ergebnis manuell in die Tabelle eintrage wird das umgedrehte Ergebnis bei dem anderen Spieler automatisch eingetragen.
Wenn das Ergebnis jedoch automatisch von unten übernommen wird klappt dies nicht mehr.

mein code schaut nun so aus

Code:

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
    Range("U8").Value = Range("AE16").Value
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
With Target
    If .Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("E8:X11")) Is Nothing Then
        x = Int(.Column / 5) - .Row + 7
        .Offset(x, -5 * x + 2 * (2 * (.Column Mod 5 = 3) + 1)) = Target
    End If
End With
Application.EnableEvents = True
End Sub

 

Ich gehe nach wie vor davon aus, daß (ohne Deaktivierung der Ereignisse) eine Endlos-Schleife produziert wird:
a) Änderung irgendwo in E8:X11 --> b) "change"-Ereignis wird ausgelöst
c) Wert wird aktualisiert --> d) "calculate"-Ereignis wird ausgelöst
e) ae16 wird in u8 übertragen --> schleife weiter in b)

Verhindern kann man die Schleife vielleicht, indem der Wert aus AE16 nur dann in U8 kopiert wird, wenn er sich vom aktuellen Wert in U8 unterscheidet. Wird, weil er gleich ist, der Wert nicht kopiert, wird auch kein "change" ausgelöst und die Schleife wird nicht endlos.

Code:

    'Application.EnableEvents = False
    If (Worksheets(sh_ziel).Range("u8").Value <> Worksheets(sh_quelle).Range("ae16").Value) Then
        Worksheets(sh_ziel).Range("u8").Value = Worksheets(sh_quelle).Range("ae16").Value
    End If
    'Application.EnableEvents = True

Die Grundursache dann wäre aber: Der Wert in U8 sorgt bei einer Änderung dafür, daß AE16 sich ändert. Da AE16 dann aber wieder in die Masse zurückgeworfen wird, die für die Änderung in AE16 zuständig ist... [loop-to-death] Das kannst aber nur Du sagen, indem Du dir ansiehst, welche Werte in Deine Formel in AE16 einfließen, direkt und auch indirekt.

Und, wie es sich anständigerweise gehört: Man bekämpft die Grundursache und doktort nicht an den Symptomen herum... ;-) Wenn Excel einen Zirkel-Bezug in einer Formel melden würde, dann kann man das Problem auch nicht mit VBA umgehen. Das Problem bleibt dann ja trotzdem das gleiche.

Aus dem geht leider nicht hervor, welche Zeilen und Spalten dort ebgebildet sind und welche Formel in AE16 steht. ;-)

Was ich aber dann auch grundlegend nicht verstehe: AE16 ist ja wohl die blau eingekreiste Zahl unter "Ergebnis". Das Ergebnis wird ja wahrscheinlich aus den Einzel-Ergebnissen links davon berechnet. Ok, nun überträgt man (per "calculate"-Ereignis) das Spiel-Ergebnis dieser einzelnen Begegnung in die Matrix aller Begegnungen weiter oben, also z.B. eben AE16 auf U8. Das Eintragen in U8 hat dann (per "change"-Ereignis) den Effekt, daß der Wert in die passende Spiegel-Zeile der Matrix (ich tippe mal auf H11) übernommen wird. Soweit ok, aber...

Welchen Sinn macht es, in der oberen Matrix dann noch manuell herumzupfuschen? Wenn unten als Gesamt-Spiel-Ergebis ein 3:2 ermittelt wird, wieso sollte ich dann in der Lage sein, oben in der Matrix ein 4:1 daraus zu machen? Und:

Ändere ich dann U8 manuell, wird früher oder später auch wieder ein "calculate" ausgelöst, was dazu führt, daß er ursprünglich berechnete Wert aus AE16 wieder über U8 drübergebügelt wird.

Ohne Zirkel-Bezug macht's also keinen Sinn, weil die Änderung nicht dauerhaft wäre. Mit Zirkel-Bezug ist's aber auch und erst recht böse... ;-)

Gruß, Michael

 

Alles korrekt.

Ich möchte gern alle (bzw. so viele wie möglich) Möglichkeiten der Ergebniseingabe abdecken.

(1) das Ergebnis wird durch die einzelnen Sätze "berechnet" und dann automatisch in die Matrix eingetragen
(2) das Ergebnis wird manuell in den unteren Bereich (unter "Ergebnis", blau) eingetragen (und nicht automatisch berechnet) und dann in die Matrix übernommen
(3) man schreibt die Ergebnisse manuell in die Matrix, ohne das diese "berechnet" werden.


edit: (3) funktioniert nicht,der gespiegelte Wert wird korrekt eingetragen und der ursprüngliche Wert verschwindet, wahrscheinlich weil in AE16 auch nichts drin steht...
(1), (2) funktioniert nicht komplett, da das gespiegelte Ergebnis nicht eingetragen wird

Das wäre in Ordnung, da falls Variante (1) genutzt wird dieses Ergebnis bevorzugt behandelt wird (besitzt höchste Priorität).

Ich seh kein Zirkelbezug:
Wert AE16 (blauer Kreis) wird berechnet falls daneben die Sätze eingetragen sind. Falls in AE16 ein Wert steht (egal ob manuell eingetragen oder per Formel berechnet) soll dieser Wert in die Matrix mit übernommen werden und zugleich "gespiegelt" werden.

Steht kein Wert in AE16, dann kann das Ergebnis nur direkt in die Matrix eingetragen wurden sein.

edit2:
Für's erste würde es jedoch ausreichen, wenn die beiden Ergebnisse korrekt in die Matrix dargestellt werden, sobald ein Ergebnis "berechnet" wurde (Variante (1), blauer Kreis). Das andere wären nur Features.

 

Das wird so nicht klappen...

Der Ablauf wäre:
a) Eintippen (oder auch nicht) von Ergebnissen der einzelnen Sätze
b) Spiel-Ergebnis wird aus den einzelnen Sätzen in AE16 berechnet
c) "calculate": AE16 wird in U8 übertragen
d) "change": U8 wird in H11 gespiegelt
Wenn im calculate (durch das change ausgelöst) jetzt nicht wieder was kopiert wird, was das change erneut auslösen würde, was wiederum... bis hierhin keine Probleme...
e) Wert in U8 wird manuell übersteuert
f) "change": U8 wird in H11 gespiegelt
g) "calculate": AE16 wird in U8 übertragen
h) "change": U8 wird in H11 gespiegelt
Wie oben nach d) sollte auch hier wieder keine Endlos-Schleife produziert werden...
Situation am Ende: AE16 hat den "falschen" Wert, der ja manuell übersteuert werden sollte. Und U8 / H11 haben ebenfalls wieder den "falschen" Wert.
Oder aber:
e) Wert in AE16 wird manuell übersteuert
f) "change": wird ausgelöst, tut aber nichts, weil nur E8:X11 überwacht werden.
g) "calculate": AE16 wird in U8 übertragen
h) "change": U8 wird in H11 gespiegelt
Situation am Ende: AE16, U8 und H11 haben den richtigen Wert, schön... Aber: AE16 hat jetzt keine Formel mehr, berechnet also nicht mehr das richtige Ergebnis, wenn in den einzelnen Sätzen was eingetragen wird. Ab jetzt haben nur noch manuelle Änderungen in U8 eine Auswirkung auf H11, weil AE16 keine Reaktion im "change" erzeugt.

Für eine Priorisierung der Eingabe-Möglichkeiten ist das "calculate" nicht geeignet, weil es nichts darüber aussagt, was gerade in welcher Reihenfolge berechnet wurde. Um die Eingaben mit Prioritäten zu versehen, wodurch andere Werte überschrieben werden können (oder auch nicht), ist wiederum nur das "change" geeignet, weil im "Target"-Parameter steht, wo aktuell gerade was geändert wurde.

Du könntest also überprüfen,
a) ob in den einzelnen Sätzen Werte aktualisiert wurden
--> Setzen der Formel zur Berechnung des Spiel-Ergebnisses:

Code:

range("ae16").formula = "=was-auch-immer"

--> Übertragen von AE16 nach U8, aber... nicht per .copy in VBA, sondern per .formula-Eigenschaft in der Zelle U8:

Code:

range("u8").formula = "=ae16"

--> Übernehmen des Wertes in die Spiegel-Zelle, ebenfalls per Formel:

Code:

range("h11").formula = "=u8"

b) ob das berechnete Spiel-Ergebnis (ae16) manuell übersteuert wurde
--> Ausgrauen (oder sogar löschen) der Werte in den einzelnen Sätzen: Wenn die Einzel-Ergebnisse nicht zum Spiel-/Gesamt-Ergebnis passen, würde ich die Ergebnispläne mit dem Turnierleiter auf äußerst unartige Weise in physischen Kontakt bringen.
--> Übertragen von AE16 nach U8:

Code:

range("u8").formula = "=ae16"

--> Übernehmen des Wertes in die Spiegel-Zelle:

Code:

range("h11").formula = "=u8"

c) ob das Einzel-Ergebnis in der Matrix (u8) manuell übersteuert wurde
--> Ausgrauen (oder sogar löschen) der Werte in den einzelnen Sätzen.
--> Übertragen von U8 nach AE16:

Code:

range("ae16").formula = "=u8"

--> Übernehmen des Wertes in die Spiegel-Zelle:

Code:

range("h11").formula = "=u8"

Das endgültige Aktualisieren überläßt man dann dem nächsten "calculate"-Ereignis...

Gruß, Michael