Finanzplan/Kontenplan mit VB

DasBinIch132_25d33b · 11. November 2019 um 06:18

Hallo ihr vielen Programmierer-Spezies,

bis jetzt hab ich immer nur n bisschen mit Excel-Funktionen hantiert, aber jetzt möchte ich einen Kontoplan erstellen, der sich mit den 0815-Formeln nicht mehr realisieren lässt.

Wie gesagt, es soll ein Kontoplan werden.
Im Tabellenblatt 1 habe ich eine Eingabe eingerichtet. D.h.
B3= Datum der Bewegung
D3= Bemerkung
F3= Betrag der Bewegung
H3= Konto der Bewegung

Nun möchte ich, das die Werte anhand des Wertes in H3 in das entsprechende Tabellenblatt eingetragen werden. Z.b. Wert 1 = Sparkasse (=Tabellenblatt 2), Wert 2 = Volksbank (=Tabellenblatt 3) usw.
Das klappt bis jetzt sehr gut.
Auch die Funktion der anschließenden Sortierung habe ich noch hinbekommen.
(D.h. wenn ich eine Bewegung am 01.06. habe und eine am 01.03 die ich im später eintrage, dann ist es in der richtigen zeitlichen Reihenfolge in den entsprechenden TBlättern sortiert.)

Mein Problem: In den Tabellenblättern möchte ich in der Spalte B den aktuellen Kontostand sehen.
Nur nach dem Sortieren muss er dementsprechend jede Zelle B die nach dem neu eingetragenen Datum liegt aktualisieren/neu berechnen.

So solls aussehen:

1 2 3 4
A 01.01.2009 100,-€ Anfangsbestand 0,-€
B 02.02.2009 A2+B4 Bewegung #1 5,-€
C …

Hier mein derzeitiger Code:

Sub Daten\_eintragen()  
Dim Zeile  
Dim Zeilr  
'nur wenn in B3 und D3 etwas drinsteht dann eintragen  
If [b3] "" And [d3] "" Then  
'Tabellenblatt ermitteln  
If [h3] = "1" Then  
Sheets("SPK").Select  
'letzte benutzte Zeile ermitteln + 1  
Zeile = Cells(Rows.Count, 1).SpecialCells(xlLastCell).Row + 1  
Zeilr = Cells(Rows.Count, 1).End(xlUp).Row  
'Daten eintragen  
Cells(Zeile, 1) = Sheets("Eingabe").Range("b3").Value  
Cells(Zeile, 2) = Cells(Zeilr, 2) + Sheets("Eingabe").Range("f3").Value  
Cells(Zeile, 3) = Sheets("Eingabe").Range("d3").Value  
Cells(Zeile, 4) = Sheets("Eingabe").Range("f3").Value  
Cells(Zeile, 5) = Sheets("Eingabe").Range("h3").Value  
  
'Sortieren  
Range(Cells(1, 1), Cells(Rows.Count, 5)).Sort \_  
Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, \_  
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, \_  
SortMethod:=xlPinYin, DataOption1:=xlSortNormal  
  
  
'Eingaben löschen  
Sheets("Eingabe").Select  
[b3] = ""  
[d3] = ""  
[f3] = ""  
[h3] = ""  
'letzte Zeile in sichtbaren Bereich holen  
Cells(Zeile, 1).Select  
Else  
'Tabellenblatt ermitteln  
If [h3] = "2" Then  
Sheets("2.VoBa").Select

…

Habt Ihr ne Idee oder Verbesserungs-/Vereinfachungsvorschlägen?

Vielen Dank im Voraus!

DasBinIch132

Reinhard_fb8cb7 · 11. November 2019 um 06:18

Hallo Ich,

soll das der ganze Code sein?

Bastle mal eine Beipielmappe mit gleichem Aufbau und paar Kontobewegungen drinnen usw. und lade sie hoch mit FAQ:2606

Gruß
Reinhard

DasBinIch132_25d33b · 11. November 2019 um 06:18

Guten Tag,

Hier meine Datei:
http://rapidshare.com/files/324387009/AKTUELL.xls.html

Freue mich auf eine Antwort

Reinhard_fb8cb7 · 11. November 2019 um 06:19

http://rapidshare.com/files/324387009/AKTUELL.xls.html

Freue mich auf eine Antwort

Hallo Ich,

ich habe in der Mappe selbst noch Blattnamen geändert und für H3 ne Gültigkeitsliste angelegt:

http://www.hostarea.de/server-12/Dezember-8ba517ab2b…

Sub Daten\_eintragen()
Dim Zeile As Long, Z As Long
'nur wenn in B3 bis H3 etwas drinsteht dann eintragen
If [b3] = "" Or [d3] = "" Or [f3] = "" Or [h3] = "" Then Exit Sub
'Tabellenblatt ermitteln
With Worksheets(Range("H3").Value)
 'letzte benutzte Zeile ermitteln + 1
 Zeile = .Cells(Rows.Count, 1).End(xlUp).Row + 1
 'Daten eintragen
 .Cells(Zeile, 1) = Range("b3").Value
 .Cells(Zeile, 3) = Range("d3").Value
 .Cells(Zeile, 4) = Range("f3").Value
 'Sortieren
 .Range("A2:smiley:" & Zeile).Sort \_
 Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, \_
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, \_
 SortMethod:=xlPinYin, DataOption1:=xlSortNormal
 .Cells(2, 2) = .Cells(2, 4)
 For Z = 3 To Zeile
 .Cells(Z, 2).Formula = "=B2+D3"
 Next Z
 .Range("b2:B" & Zeile).Value = .Range("b2:B" & Zeile).Value
 'Eingaben löschen
 Range("B3,D3,F3,H3").ClearContents
 MsgBox "Eingetragen"
 Range("B3").Value = Date
End With
End Sub

Gruß
Reinhard

DasBinIch132_25d33b · 11. November 2019 um 06:19

Hi Reinhard,

vielen Dank für deine Hilfe!
Leider funktioniert das so noch nicht ganz.
Zwar wird in Spalte B jetzt etwas errechnet, allerdings nicht das was ich möchte.

Mit der Schleife
For Z = 3 To Zeile
.Cells(Z, 2).Formula = „=B2+D3“
Next Z
Wird in jeder Zelle der Spalte B B2+D3 errechnet. Er müsste aber im Prinzip immer einen „weiterhüpfen“.
B2 = B1+D2
B3 = B2+D3
B4 = B3+D4 usw.

Gibt es vllt. einen Befehl der umschrieben heisst: Cells(ZeileDrüber, 2) + Cells(AktuelleZeile, 3)
?

MfG DasBinIch132

Reinhard_fb8cb7 · 11. November 2019 um 06:19

Leider funktioniert das so noch nicht ganz.
Zwar wird in Spalte B jetzt etwas errechnet, allerdings nicht
das was ich möchte.

hallo Ich,

hast Recht, naja mit B2 = B1+D2 nicht weil B1 die Überschrift ist

Sub Daten\_eintragen()
Dim Zeile As Long, Z As Long
'nur wenn in B3 bis H3 etwas drinsteht dann eintragen
If [b3] = "" Or [d3] = "" Or [f3] = "" Or [h3] = "" Then Exit Sub
'Tabellenblatt ermitteln
With Worksheets(Range("H3").Value)
 'letzte benutzte Zeile ermitteln + 1
 Zeile = .Cells(Rows.Count, 1).End(xlUp).Row + 1
 'Daten eintragen
 .Cells(Zeile, 1) = Range("b3").Value
 .Cells(Zeile, 3) = Range("d3").Value
 .Cells(Zeile, 4) = Range("f3").Value
 'Sortieren
 .Range("A2:smiley:" & Zeile).Sort \_
 Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, \_
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, \_
 SortMethod:=xlPinYin, DataOption1:=xlSortNormal
 .Cells(2, 2) = .Cells(2, 4)
 If Zeile 2 Then
 .Range("B3:B" & Zeile).Formula = "=B2+D3"
 .Range("b2:B" & Zeile).Value = .Range("b2:B" & Zeile).Value
 Else
 .Range("B2") = .Range("D2")
 End If
 'Eingaben löschen
 Range("B3,D3,F3,H3").ClearContents
 MsgBox "Eingetragen"
 Range("B3").Value = Date
End With
End Sub

Gruß
Reinhard

Mit der Schleife
For Z = 3 To Zeile
.Cells(Z, 2).Formula = „=B2+D3“
Next Z
Wird in jeder Zelle der Spalte B B2+D3 errechnet. Er müsste
aber im Prinzip immer einen „weiterhüpfen“.
B2 = B1+D2
B3 = B2+D3
B4 = B3+D4 usw.

Gibt es vllt. einen Befehl der umschrieben heisst:
Cells(ZeileDrüber, 2) + Cells(AktuelleZeile, 3)
?

MfG DasBinIch132

DasBinIch132_25d33b · 11. November 2019 um 06:19

Unglaublich, endlich funktioniert es!!!

Ich dank dir vielmals!

Jetzt muss ich im ersten Tabellenblatt nur noch ne kleine Ausgabe machen, dass ich ein Datum eingeben und er mir den Kontostand ausgibt.
Aber ich denk, dass wird halb so schwer werden…

Iwas in Richtung: Suche im Bereich von „Eingabe-Konto“ von unten an nach „Eingabe-Datum“ in Spalte A und gibt dazugehörige Spalte B aus…

Wie gesagt, besten Dank!!

MfG Ich :]

DasBinIch132_25d33b · 11. November 2019 um 06:20

Ich nochmal,
Leider ist es für mich doch nicht ganz so einfach, die Tabelle zu
vervollständigen.

Möchte grad ne Kontostand-abfrage machen, allerdings nimmt Excel den
Befehl nicht immer.

Sub Kontostand_aktualisieren()

Dim Stand As Long

'letzte Zeile
Stand = Cells(Rows.Count, 1).End(xlUp).Row

'CommerzH
Sheets(1).Cells(4, 9) = Sheets(2).Range(„B1:B“ & Stand)

End Sub

Die „Suchfuntion“ klappt auch noch nicht…

Und für die Speziell Suche nach Datum hab ich leider keine Ahnnung. Hab mir diverse Foren durchgelesen, aber nichts passt und nichts kann ich entsprechend abändern.
Habt ihr vllt etwas vorgefertigt? Bzw. 'nen Gedankenanstoß?

MfG Ich

Reinhard_fb8cb7 · 11. November 2019 um 06:22

Möchte grad ne Kontostand-abfrage machen, allerdings nimmt
Excel den
Befehl nicht immer.

Hallo Ich,

gib das Folgende im Blatt Eingabe ein und formatiere die zwei Spalten entsprechend als datum und zahl/Währung.

Wenn das klappt sag Bescheid, dann schau ich mal was du da mit Suchfunktion meinst.

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Dezember-8ba517ab2b.xls]!Eingabe
 │ A │ B │ C │
──┼───────┼───────────────────────┼────────┤
5 │ Konto │ letzter
Buchungstag │ Betrag │
──┼───────┼───────────────────────┼────────┤
6 │ CoBaH │ │ │
──┼───────┼───────────────────────┼────────┤
7 │ CoBaN │ │ │
──┼───────┼───────────────────────┼────────┤
8 │ Spk │ │ │
──┼───────┼───────────────────────┼────────┤
9 │ VoBa │ │ │
──┴───────┴───────────────────────┴────────┘
Zahlenformate der Zellen im gewählten Bereich:
A5:A9
haben das Zahlenformat: TT.MM.JJ;@
B5
haben das Zahlenformat: \_-\* #.##0,00 [$€]\_-;-\* #.##0,00 [$€]\_-;\_-\* "-"?? [$€]\_-;\_-@\_-
B6:B9
haben das Zahlenformat: TT.MM.JJ
C5
haben das Zahlenformat: Standard
C6:C9
haben das Zahlenformat: 0,00

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Dazu benutze diesen Code:

Option Explicit

Sub Daten\_eintragen()
Dim Zeile As Long, Z As Long
'nur wenn in B3 bis H3 etwas drinsteht dann eintragen
If [b3] = "" Or [d3] = "" Or [f3] = "" Or [h3] = "" Then Exit Sub
'Tabellenblatt ermitteln
With Worksheets(Range("H3").Value)
 'letzte benutzte Zeile ermitteln + 1
 Zeile = .Cells(Rows.Count, 1).End(xlUp).Row + 1
 'Daten eintragen
 .Cells(Zeile, 1) = Range("b3").Value
 .Cells(Zeile, 3) = Range("d3").Value
 .Cells(Zeile, 4) = Range("f3").Value
 'Sortieren
 .Range("A2:smiley:" & Zeile).Sort \_
 Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, \_
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ', \_
 'SortMethod:=xlPinYin, DataOption1:=xlSortNormal
 .Cells(2, 2) = .Cells(2, 4)
 If Zeile 2 Then
 .Range("B3:B" & Zeile).Formula = "=B2+D3"
 .Range("b2:B" & Zeile).Value = .Range("b2:B" & Zeile).Value
 Else
 .Range("B2") = .Range("D2")
 End If
 'Eingaben löschen
 Range("B3,D3,F3,H3").ClearContents
 MsgBox "Eingetragen"
 Range("B3").Value = Date
 Call Kontostand
End With
End Sub
'
Sub Kontostand()
Dim Zei As Long, Blätter, B
Blätter = Array("CoBaH", "CoBaN", "Spk", "VoBa")
For B = 0 To UBound(Blätter)
 Zei = Worksheets(Blätter(B)).Cells(Rows.Count, 1).End(xlUp).Row
 If Zei \> 1 Then
 Worksheets("Eingabe").Cells(B + 6, 2) = Worksheets(Blätter(B)).Cells(Zei, 1)
 Worksheets("Eingabe").Cells(B + 6, 3) = Worksheets(Blätter(B)).Cells(Zei, 2)
 End If
Next B
End Sub

Gruß
Reinhard