Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
880to884
880to884
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Berechnung bei Eintrag

Forumthread: Berechnung bei Eintrag

Berechnung bei Eintrag
22.06.2007 13:13:53
Gunnar
Hallo Leute,
für folgendes Problem hab ich noch keine wirkliche praktikable Lösung:
Ich habe in einer Tabelle in den Zeilen verschiedene Budgetpositionen und in den Spalten die Monate über die gesamte Laufzeit des Projektes. Es ist noch nicht bei allen Positionen klar, ob diese steuerpfichtig sind oder nicht.
Wenn nun in Spalte E vor der jeweiligen Budgetposition ein "x" eingetragen wird, soll auf alle Beträge die in der jew. Zeile der Budgetposition (z.B. F5:AJ5, F7:AJ7,...) eingetragen sind eine 18%-ige Umsatzsteuer berechnet werden und diese in Zeile F30:AJ30 summiert / separat ausgewiesen werden.
Ich hoffe, ich hab mich halbwegs verständlich ausgedrückt.
Vielen Dank im Voraus.
Gruss Gunnar

Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Berechnung bei Eintrag
22.06.2007 14:19:15
ingUR
Hallo, Gunnar,
ohne jetzt die Sache in allen Einzelheiten getestet zu haben, könntest Du vielleicht das nachfolgende Grundgerüst an einer Kopie Deiner Tabelle testen:

Option Explicit
Dim prevValue As Variant
Const USTP As Double = 18
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngC As Range, ustF As Double
With Target
IF (.Rows

beide Prozedutren sind im VBA-Projekt in den Tabelelnornder Der betreffenden Kopie der Arbeitsmappe ein zufügen.
Während ich bei den Spalten Dienen Angaben beim Code entsprechnded berücksichtigen konnte (Column=5 ist die Spalte "E"), mußt Du ggf. den Wert für die Zeile in der Anweisungszeile
IF (.Rows<2) Or (Not .Column = 5) Then Exit Sub in beiden Prozeduren anpassen, da mit .Row<2 festgelegt wird, dass vor der Zeile 2 keine Kostenwerte vorhanden sind (z.B. eine Kopfzeile).
Gruß,
Uwe

Anzeige
AW: Berechnung bei Eintrag
22.06.2007 16:02:00
Gunnar
Hallo Uwe,
vielen Dank für die prompte Hilfe, aber der Code hängt sich bei der Prozedur
Sub Worksheet_SelectionChange in der Zeile "If(.Rows..." auf.
Fehlermeldung: "Fehler beim Kompilieren. Unzulässiger oder nicht ausreichend definierter Verweis"
Ich habe lediglich den Datenbereich angepasst (G:AK). Alles andere passt. In Zeile 1 stehen Überschriften und in Spalte 5 würde das "x" eingetragen werden.
Und entschuldige die möglicherweise etwas blöde Frage: Soweit ich die Prozeduren verstehe, berechnen sie die Steuern auf die jeweilige Budgetposition. Mir fehlt irgendwie noch der separate Ausweis / das Summieren aller Steuerbeträge in Zeile 32.
Gruss Gunnar

Anzeige
AW: Berechnung bei Eintrag
22.06.2007 16:45:00
ingUR
Hallo, Gunnar,
das war mein Kopierfehler, denn während ich in der WorkSheet_Change-Prozedur den With Target ... End With-Block einsetze, in demm jeder führende Punkt durch den Objektbezeichner Target ergänzt wird, habe ich diese Technik nicht in der WorkSheet_ChangeSelection verwendet, dennoch unzulässigerweise die abgekürzte Schreibweise genutzt.
Richtig ist also:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target.Column = 5 Or Target.Row


oder 


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
with Target
If Not .Column = 5 Or .Row


Zur Umsetzung der Saldierung in der Zeile 30, besteht die Möglichkeit, bei Änderung der Zelle, den UST-Betrag in der Zeile zu Addieren bzw. zu subtrahieren, je nach "X":


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngC As Range, ustF As Double, Betrag As Double
'Exit Sub
With Target
If Not .Column = 5 Then Exit Sub
If .Value = prevValue Then Exit Sub
If (.Value = " " Or .Value = "") And prevValue = "x" Then
ustF = 1 / (1 + USTP / 100)
For Each rngC In Range("F" & .Row & ":AJ" & .Row)
If Not IsEmpty(rngC) Then
Betrag = rngC.Value
rngC = rngC * ustF
Cells(30, rngC.Column) = Cells(30, rngC.Column) + (rngC - Betrag)
End If
Next rngC
ElseIf UCase(.Value) = "X" And prevValue = "" Then
ustF = (1 + USTP / 100)
For Each rngC In Range("F" & .Row & ":AJ" & .Row)
If Not IsEmpty(rngC) Then
Betrag = rngC.Value
rngC = rngC * ustF
Cells(30, rngC.Column) = Cells(30, rngC.Column) + (rngC - Betrag)
End If
Next rngC
End If
prevValue = .Value
End With
End Sub


Gruß,
Uwe

Anzeige
AW: Berechnung bei Eintrag
22.06.2007 17:21:00
Gunnar
Hi Uwe,
funktioniert grundsätzlich spitze, hat aber ein paar Macken...
1. In der Zelle, in der das Budget selbst eingetragen ist, wird momentan die MwSt dazugerechnet, dort soll aber immer nur der Nettobetrag stehen.
2. Wenn Werte in aufeinanderfolgenden Monaten eingetragen werden, rechnet er die MwSt hoch (also z.B. bei Budget G9 = 100 G32 = 18, H9 = 100 H32 = 36 usw.)
3. Der Code reagiert nicht, wenn Budgetwerte wieder gelöscht werden, was zu dem Ergebnis führt, dass die Steuer stehen bleibt, wenn der Budgetwert gelöscht wurde.
4. Wenn das x zeitlich vor dem Budget eingetragen wurde, rechnet er garnicht. Wird dann das x gelöscht, zieht er (richtigerweise, aber in dem Moment unpassend) 18% ab.
5. Einzelne Werte bleiben stehen, obwohl Budget und x gelöscht wurden.
Ich bin dir riesig dankbar, dass du dich dem Problem angenommen hast und hoffe dass du dir den Spass trotz meiner "Nörgeleien" nochmal anschaust.
Vielen Dank & schönes WE.
Gruss Gunnar

Anzeige
AW: Berechnung bei Eintrag
22.06.2007 18:36:00
ingUR
Hallo, Gunnar,
danke für die Rückmeldung.
Für die Flexibilität die Du anstrebst, scheint es mir geeigneter, wenn Du die Matrix des Tabellenblattes im einem Array im VBA-Hintergrund verwaltest und damit bei Änderung die Sichtmaslke neu aufbaust, denn das Löschen/Ändern von Positionen bedingt, dass Du jede Zelle auf Änderung überwachst und dann ensprechend reagierst.
Allerding könntest Du auch, wenn in der Tabelle in jedem Fall die Nettowerte erhalten bleiben sollen, mit einer Summenproduktformel, die die Spalte E als Wahrheitsvektor einbezieht, den Zellenwert der Zeile 30 einer Spalte bestimmen und könntest so auf VBA verzichten:
Zelle F30:: =SUMMENPRODUKT(($E2:$E29="x")*0,18;F2:F29)
und dann nach rechts durch Ziehen kopieren bis zur Spalte AJ
Die VBA-Version war nur angedacht, weil ich meinte, dass die Tabellenwerte verändert werden sollten.
Gruß,
Uwe

Anzeige
AW: Berechnung bei Eintrag
25.06.2007 12:03:23
Gunnar
Hallo Uwe,
warum einfach, wenn's umständlich auch geht. ;-) Auf die Formel bin ich nicht gekommen.
Funktioniert spitze und liefert genau die Ergebnisse, die ich wollte.
Vielen Dank!!!
Gruss Gunnar
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige