Office: Zirkelbezug vermeiden

Hallo,

ich habe eine Tabelle die, wenn fertig, von vielen Benutzern verwendet werden soll. In der Tabelle gibt es Eingabefelder für Beiträge.

Bsp.
A1 = monatlich
B1 = jährlich

Wenn ich nun in Zelle A1 einen Beitrag eingebe möchte ich das in B1; "=A1*12" gerechnet wird. Andersrum möchte ich, wenn in B1 ein Beitrag eingegeben wird in A1; "=B1/12" berechnet wird. Leider bekomme ich immer einen Zirkelbezug, egal wie ich es drehe.

Ich weiß das ich es über die Optionen ausschalten kann. Aber das möchte ich gerne vermeiden da, wie oben erwähnt, einige Leute die Tabelle nutzen sollen und ich nicht jedem zumute diese Einstellung vorzunehmen.

Die Tabelle arbeitet mit Formeld und Makros.

Für Lösungsvorschläge wäre ich sehr dankbar.


mfg Marcel
<.o)

 

Hallo,

dein Vorhaben ist mit einer Formel nur dann zu realisieren wenn du noch eine Hilfszelle dazunimmst und dort mit einer Wenn-Formel arbeitest, z.B. =WENN(A1<>"";A1/12;B1*12)

Ansonsten kannst du das nur mit VBA lösen.

 

Hallo Marcel,

mittels Change-Ereignis in VBA geht das direkt:

1) Falls Du dieses Verhalten nur in einem bestimmten Arbeitsblatt installieren möchtest, müsstest Du im VBA-Editor folgenden VBA-Code in das Code-Modul dieses Arbeitsblattes kopieren:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
    Select Case Target.Row
      Case 1: Cells(1, 2).Value = Cells(1, 1).Value * 12 'monatliche Eingabe in A1, Berechnung in B1
      Case 2: Cells(1, 1).Value = Cells(1, 2).Value / 12 'jährliche Eingabe in B1, Berechnung in A1
    End Select
  End If
End Sub

2) Falls Du dieses Verhalten in allen Abeitsblättern installieren möchtest, müsstest Du im VBA-Editor folgenden VBA-Code in das Code-Modul der Arbeitsmappe 'DieseArbeitsmappe' kopieren:

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Column = 1 Then
    Select Case Target.Row
      Case 1: Cells(1, 2).Value = Cells(1, 1).Value * 12 'monatliche Eingabe in A1, Berechnung in B1
      Case 2: Cells(1, 1).Value = Cells(1, 2).Value / 12 'jährliche Eingabe in B1, Berechnung in A1
    End Select
  End If
End Sub

 

@ Peter

Mit Formeln wird das meines erachtens nicht gehen da ich egal wie einen Zirkelbezug habe.

@Exl

Der Ansatz ist schonmal interessant.
Allerdings wenn ich den ersten Code nehme dann funktioniert es nur bei Dateneingabe in Zelle A1. Wenn ich in B1 etwas eingebe rechnet er nicht weiter.
Dazu kommt das mir der Bezug "cells(x, x)" nicht ausreichen wird da die Tabelle dafür zu groß ist. Kann ich auch dierekt auf bestimmte Zellen zugreifen?!
Habe es über den "Range" Bezug probiert - aber ohne Erfolg.

mfg <.o)

 

Hallo Marcel,

es ist mir leider beim Target-Objekt in Zeile 2+3 ein Fehler passiert. Korrekt muss das Makro in der Variante 1) so lauten:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Then
    Select Case Target.Column
      Case 1: Cells(1, 2).Value = Cells(1, 1).Value * 12 'monatliche Eingabe in A1, Berechnung in B1
      Case 2: Cells(1, 1).Value = Cells(1, 2).Value / 12 'jährliche Eingabe in B1, Berechnung in A1
    End Select
  End If
End Sub

2) In der 2. Variante steckt der gleiche Fehler drinnen, also bitte analog korrigieren.

3) Ferner schreibst Du, dass der Bezug "cells(x,x)" nicht ausreichen wird, da die Tabelle dafür zu groß ist. Ich hatte mich beim Makro an Deine Vorgaben gehalten: Eingabe in Zelle A1 bzw. B1 und Berechnung in der jeweils anderen.
Um das Makro präziser abstimmen zu können, müsste ich genauere Details von Deiner Tabelle wissen.

 

SUPER das funktioniert schon mal...

Das Problem ist das meine Tabelle mittlerweile ca. 1000 Zeilen umfasst und ich in ca 100 Zellen diese abfrage tätigen muss.

Bsp:
mtl = K10, K15; K20; etc... sowie S10, S15, S20; etc
jhrl. = M10, M15; M20; etc... sowie U10, U15, U20; etc

das heißt mit dem "cell x,y" Bezug wäre das ganz schön aufwendig. Besser wäre es auf die Zelle die ich abfragen will direkt zugreifen zu können.

Bisher habe ich viel mit dem "Range("K10")" Befehl gearbeitet...


war das verständlicher?! oOo...
mfg

 

Hallo Marcel,

habe ich Dich recht verstanden: Du möchtest in Spalte K eine Monatseingabe machen und Spalte M den Jahreswert erhalten, bzw. bei Eingabe in Spalte M in Spalte K den Monatswert berechnen.
Gleiches willst Du mit den Spalten S (monatl.) und U (jährl.) machen.
Dann müsste folgendes Makro in etwa passen:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'  Spalte K monatlich <-> Spalte M jährlich
'  Spalte S monatlich <-> Spalte U jährlich
   Dim Zl As Long, Sp As Long
   Zl = Target.Row
   Sp = Target.Column
   Select Case Sp
     'Eingabe in Spalte K bzw. S (=mtl.), Berechnung in Spalte M bzw. U(=jährl.)
     Case 11, 19: Cells(Zl, Sp + 2).Value = Cells(Zl, Sp).Value * 12
     'Eingabe in Spalte M bzw. U (=jährl.), Berechnung in Spalte K bzw. S (=mtl.)
     Case 13, 21: Cells(Zl, Sp - 2).Value = Cells(Zl, Sp).Value / 12
   End Select
End Sub

Dieses Makro wiederum ins Code-Modul des betreffenden Arbeitsblattes speichern.
Dieses Makro ist jetzt allerdings in jeder Zeile des Arbeitsblattes aktiv, also bei Eingabe in eine der 4 Spalten K,M,S,U wird die dazugehörige Spalte berechnet (monatl. bzw. jährl.) und somit geändert.

Falls das Makro für alle Arbeitsblätter der Arbeitsmappe gelten sollte, müsste als Prozedurkopf

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

genommen werden (und der Rest lautet wiederum genaus so) und das Ganze im Code-Modul von 'DieseArbeitsmappe' gespeichert werden.

 

GANZ LIEBEN DANK !!!

Hätt echt nicht gedacht das das geht. Habe mir das Makro noch auf die richtigen Spalten eingestellt und dadurch wenigstens ein wenig verstanden wie es funktioniert - aber selber schreiben hätt ich das nicht gekonnt...