Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1228to1232
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Codelösung gesucht oder ähnliches

Codelösung gesucht oder ähnliches
Thomas
Hallo Excelfreunde,
ich suche eine VBA- Lösung und brauch da eure Hilfe. Wie bekomme ich Miete und Nebenkosten anhand dem Einzugsdatum (F4) und Auszugsdatum (H4) in mein Formular auch wenn der Einzug zum 15. ist?
Habe die Beträge mal von Hand in mein Formular eingetragen.
Hoffe es kann mir jemand helfen.
Rechnung
 ABCDEFGHIJKLMN
1  Jahresabrechnung-2010         
2                           
3    Abrechnungsperiodevom:01.01.bis:31.12.           
4    Abrechnunszeitraumvon:15. 01.bis:31. 07.           
5  Lerchenstr. 58                       
6  2                       
7                           
8  KostenGesamtSchlüsselIhre KostenMieteNK        2010   
9  Gebäudebrandversicherung1112,45n. MEA49,77300,0085Jan      Frio1   
10  Hafpflichtversicherung mit Ölvers.414,96n. MEA18,57600,00190Feb      Belegung'6.5<<kommt per Code
11  Wohngebäudeversicherung768,59n. MEA34,39600,00190Mrz      Miete pro Monat600<<kommt per Code
12  Glasversicherung118,52n. MEA5,30600,00190Apr      NK pro Monat190<<kommt per Code
13  Kaminfeger46,64n. MEA2,09600,00190Mai           
14  Heizung/warmwasser lt. Brunata'7770.5nach Verb.271,00600,00190Jun           
15  Gartenbetreuung340,00nach Einh.18,42600,00190Jul           
16  Gartenbedarf31,99n. MEA1,43    Aug           
17  Wasser Garten15,28nach Einh.0,75    Sep           
18  Wasser/ Abwassergeb. In cbm3006,47102,860 m³182,63    Okt           
19  Gundgebühren / Abfallbeseitigung684,00Personen26,14    Nov           
20  Leerung / Abfallbeseitigung'588.4Personen14,86    Dez           
21  Müllbetreueng300,00nach Einh.16,25                 
22  Allgemeinstrom'582.09n. MEA26,04                 
23  Treppenhaus Fensterreinigung'157.08nach Einh.'7.735                 
24  Überprüfung Feuerlöscher0,00nach Einh.0,00                 
25  Wartungsvertrag Rückstauklappe'70.01nach Einh.3,45                 
26  Wartung Heizung'320.11n. MEA14,32                 
27  Wartungsvertrag Kaltwasserzähler'119.12nach Einh.5,87                 
28  Einzug und Auszugspauschale153,38  76,69                 
29  Rechnung Kraft0,00nach Einh.0,00                 
30  Rechnung Wild91,63nach Einh.4,51                 
31  Rechnung Jost0,00nach Einh.0,00                 
32  Sonstiges0,00nach Einh. 0,00                 
33  Stellplatz300,00  '162.5                 
34  Miete7200'6.53900,00                 
35  Grundsteuer109,89  59,52                 
36  Gutschrift-3,6  -0,21                 
37                           
38                           
39    24297,51  4902,033900,001225,00             
40                           
41        5125,00                 
42                           
43  Der Betrag wird Ihnen auf Ihr Konto überwiesen.    222,97                  
44                           
45                           
46                         
47                           

verbundene Zellen
B1:I1
C3:D3
C4:D4
E46:F46

verwendete Formeln
Zelle Formel Bereich
B1 =VERKETTEN("Jahresabrechnung" & - ( L8)) 
C39 =SUMME(C9:C37) 
E39 =SUMME(E9:E37) 
F39 =SUMME(F9:F20) 
G39 =SUMME(G9:G20) 
E41 =F39+G39 
B43 =WENN(E43>0;"Der Betrag wird Ihnen auf Ihr Konto überwiesen.";"Bitte überweisen Sie den Betrag auf mein Konto.") 
E43 =E41-E39 
B45 =WENN(E43<=-100;"Ihre neue monatliche Nebenkostenvorauszahlung (gerundet)";"") 
E45 =WENN(E43<=-100;(E39-G39)/M10;"") 
B46 =WENN(E43<=-100;"Diese Vorauszahlung ist zu Zahlen ab dem";"") 
E46 =WENN(E43<=-100;MONATSENDE(HEUTE();1)+1;"") 

definierte Namen
Name Bezieht sich auf Tabelle Z1S1-Formel
Print_Area=Rechnung!$A$1:$J$47Rechnung=Rechnung!Z1S1:Z47S10

Zahlenformate
Zelle Format Wert
F4 TT. MM.40193
H4 TT. MM.40390
C9 0,001112,45
E9 0,0049,7728670833333
F9 0,00300
C10 0,00414,96
E10 0,0018,566002
F10 0,00600
C11 0,00768,59
E11 0,0034,3879975833333
F11 0,00600
C12 0,00118,52
E12 0,005,30278233333333
F12 0,00600
C13 0,0046,64
E13 0,002,08675133333333
F13 0,00600
C14 0,00'7770.5
D14 #.##0,00" m³"nach Verb.
E14 0,00271
F14 0,00600
C15 0,00340
E15 0,0018,4166666666667
F15 0,00600
C16 0,0031,99
D16 TT.MM.JJJJn. MEA
E16 0,001,43128591666667
F16 0,00 
C17 0,0015,28
E17 0,000,752424242424242
F17 0,00 
C18 0,003006,47
D18 #.##0,000" m³"102,86
E18 0,00182,63
F18 0,00 
C19 0,00684
E19 0,0026,14
F19 0,00 
C20 0,00'588.4
E20 0,0014,86
F20 0,00 
C21 0,00300
E21 0,0016,25
C22 0,00'582.09
E22 0,0026,04367675
F22 0,00 
C23 0,00'157.08
E23 0,00'7.735
F23 0,00 
C24 0,000
E24 0,000
F24 0,00 
N24 0,00% 
C25 0,00'70.01
E25 0,003,44746212121212
F25 0,00 
C26 0,00'320.11
E26 0,0014,3222549166667
F26 0,00 
C27 0,00'119.12
E27 0,005,86575757575758
F27 0,00 
C28 0,00153,38
E28 0,0076,69
F28 0,00 
C29 0,000
E29 0,000
F29 0,00 
C30 0,0091,63
E30 0,004,51208333333333
F30 0,00 
C31 0,000
E31 0,000
F31 0,00 
C32 0,000
E32 0,000
F32 0,00 
C33 0,00300
E33 0,00'162.5
F33 0,00 
E34 0,003900
F34 0,00 
E35 0,0059,52375
F35 0,00 
F36 0,00 
F37 0,00 
F38 0,00 
C39 0,0024297,51
E39 0,004902,02676185606
F39 0,003900
G39 0,001225
E41 0,005125
E43 0,00_ ;[Rot]-0,00 222,973238143939
E45 0 
E46 TT.MM.JJJJ 
Zellen mit Format Standard werden nicht dargestellt

Daten, Gültigkeit
Zelle Zulassen Daten Wert1 Wert2 Leere Zellen ignorieren Zellendropdown Titel Eingabemeldung Eingabemeldung Titel Fehler Fehlermeldung
L8Liste   2009;2010;2011;2012;2013;     Wahr   Wahr          
L9Liste   Whg1;Whg2;Whg3;Pfarrtor;Frio;Frio1;     Wahr   Wahr          

Tabellendarstellung in Foren Version 5.37


Gruß Thomas

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Codelösung gesucht oder ähnliches
03.09.2011 15:11:34
fcs
Hallo Thomas,
alle Gesamt-Kosten die in der Abrechnungsperiode angefallen sind und zeitabhängig verteilt werden können/müssen multiplizierst du mit dem Faktor TageAbrechnungsZeitraum/TageAbrechnungsperiode.
Vorsicht bei den nach Personen umgelegten Kosten. Wenn hier keine Einzelabrechnung vorliegt, dann muss du als du als Umlageschlüssel die Personenzahltage einführen, damit unterschiedliche Personenzahlen in den Abrechnungszeiträumen korrekt berücksichtigt werden.
Gruß
Franz
AW: Codelösung gesucht oder ähnliches
03.09.2011 20:43:18
Thomas
Hallo Franz,
mir geht es nur um die Verteilung in den Spalten F9 : G20 in abhänigkeit des Datums aus F4 und H4 also Miete und Nebenkosten.
Beim Lesen Deines Beitrags ist mir ein Denkfehler meinerseits aufgefallen. Ich kann ja nicht strickt nach diesem Schema verteilen, denn gehe ich davon aus das wenn ich im Februar die Jahresabrechnung mache und es ergibt sich eine höhere Nachzahlung die sich per Formel errechnet und dann ein Datum ausgibt wann er dann die neue Rate für die NK zu zahlen hat so kann ich nicht einfach mir den Miete und Nk für einen Monat in eine Hilfszelle schreiben lassen.
1000 Dank Dir Franz für die Hilfe ich muss es anderst lösen ggf. so wie ich es bisher gemacht habe (13 weitere Zeilen pro Whg). Ich muss mir eine neue Tabelle anlegen hilft alles nix.
Gruß Thomas
Anzeige
AW: Codelösung gesucht oder ähnliches
04.09.2011 13:02:11
Thomas
Hallo zusammen,
so hab nun meine Tabelle erstellt. Wie bekomme ich nun die Zahlen in mein Formular ohne Formeln anhand des Datums (Abrechnungszeitraum)? Meine Tabelle kann ich dann durchgängig Pflegen unabhänig ob eine Whg leer ist, ob ein Mieterwechsel statt findet oder ob die Miete oder NK erhöht wird.
Also wie bekomme ich aus diesem Bereich
Miete_NK
 ABCDE
45WhgMonatJahrNKMiete
461Jan2009200650
47  Feb  200650
48  Mrz  200650
49  Apr  200650
50  Mai  200650
51  Jun  200650
52  Jul  200650
53  Aug  200650
54  Sep  200650
55  Okt  200650
56  Nov  200650
57  Dez  200650

verwendete Formeln
Zelle Formel Bereich
D46 {=SUMME(WENN($A$3:$A$38=A46;WENN($B$3:$B$38=B46;WENN($C$2:$M$2=C46;$C$3:$M$38;0))))} $D$46
E46 {=SUMME(WENN($A$3:$A$38=A46;WENN($B$3:$B$38=B46;WENN($C$1:$M$1=C46;$C$3:$M$38;0))))} $E$46
D47 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B47;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$47
E47 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B47;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$47
D48 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B48;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$48
E48 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B48;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$48
D49 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B49;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$49
E49 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B49;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$49
D50 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B50;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$50
E50 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B50;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$50
D51 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B51;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$51
E51 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B51;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$51
D52 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B52;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$52
E52 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B52;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$52
D53 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B53;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$53
E53 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B53;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$53
D54 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B54;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$54
E54 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B54;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$54
D55 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B55;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$55
E55 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B55;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$55
D56 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B56;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$56
E56 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B56;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$56
D57 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B57;WENN($C$2:$M$2=$C$46;$C$3:$M$38;0))))} $D$57
E57 {=SUMME(WENN($A$3:$A$38=$A$46;WENN($B$3:$B$38=B57;WENN($C$1:$M$1=$C$46;$C$3:$M$38;0))))} $E$57
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Daten, Gültigkeit
Zelle Zulassen Daten Wert1 Wert2 Leere Zellen ignorieren Zellendropdown Titel Eingabemeldung Eingabemeldung Titel Fehler Fehlermeldung
C46Liste   2009;2010;2011;2012;2013;2014;     Wahr   Wahr          

Tabellendarstellung in Foren Version 5.37


die Zahlen in diesen Bereich in Abhängigkeit des Datums?
Rechnung
 CDEFGH
4Abrechnunszeitraumvon:01.06.bis:31.12.
5           
6           
7  85       
8GesamtSchlüsselIhre KostenMieteNK 
91097,83n. MEA52,90    Jan
10414,46n. MEA19,97    Feb
11758,54n. MEA36,55    Mrz
12118,52n. MEA5,71    Apr
1361,65n. MEA'2.9705025    Mai
149509,54nach Verb.369,89    Jun
15400,00nach Einh.23,33    Jul
160,00n. MEA0,00    Aug
17'11.13nach Einh.0,59    Sep
18'2916.642,893 m³182,71    Okt
19'814.8Personen29,13    Nov
20'590.6Personen'41.11    Dez

verbundene Zellen
C4:D4

definierte Namen
Name Bezieht sich auf Tabelle Z1S1-Formel
Print_Area=Rechnung!$A$1:$J$47Rechnung=Rechnung!Z1S1:Z47S10

Tabellendarstellung in Foren Version 5.37


Gruß Thomas
Anzeige
AW: Codelösung gesucht oder ähnliches
04.09.2011 15:26:15
fcs
Hallo Thomas,
es wäre wesentlich einfacher, wenn du hier eine Datei mit den beiden Tabellenblättern Rechnung und Miete_NK hochladen würdest, dann braucht man das nicht alles nachbauen, um ein wenig mit entsprechenden Formeln und Makros zu experimentieren.
Persönliche Informationen bzw. für die Promlösung nicht relevante Informationen in den Blättern kannst du ja löschen oder anonymisieren.
Gruß
Franz
AW: Codelösung gesucht oder ähnliches
05.09.2011 11:15:32
Thomas
Hallo Franz,
es ist schwer hier jedem gerecht zu werden. Hajo will nicht erst eine Datei runter Laden müssen um helfen zu können. Ich finde es auch alle mal besser anhand einem Beispiel.
Anbei die Datei.
https://www.herber.de/bbs/user/76462.xls
Gruß Thomas
Anzeige
AW: Codelösung gesucht oder ähnliches
05.09.2011 14:53:01
fcs
Hallo Thomas,
nachfolgende Ergänzungen im Makro sind nötig, um zusätzlich die Daten aus dem Blatt "Miete_NK" Bereich "D46:E57" ins Blatt "Rechnung" zu übertragen.
Zusätzlich hab ich im Blatt "Miete_NK" dem Bereich "D46:E57" den Namen "NK_Miete" zugewiesen.
Gruß
Franz

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$8" Or Target.Address = "$L$9" Then
Application.EnableEvents = False
Range("B9:E37").ClearContents   'Hier kann auch ("A1:E20") stehen
Range("F9:G20").ClearContents
If Range("L8") > 0 And Range("L9")  "" Then
ReadValues
Read_NK_Miete                  ' neue Zeile !!!!
End If
Application.EnableEvents = True
End If
End Sub
Sub Read_NK_Miete()           'zusätzliche Prozedur !!!
Dim StartDatum As Date, EndeDatum As Date
Dim Monat_Start As Integer, Monat_Ende As Integer
Dim NK As Double, Miete As Double, Faktor As Double
Dim Jahr As Integer, Monat As Integer
With Worksheets("Rechnung")
StartDatum = Range("F4").Value
EndeDatum = Range("H4").Value
Monat_Start = Month(StartDatum)
Monat_Ende = Month(EndeDatum)
For Monat = Monat_Start To Monat_Ende
With Worksheets("Miete_NK").Range("NK_Miete") ' Range("D46:E57")
NK = .Cells(Monat, 1).Value
Miete = .Cells(Monat, 2).Value
End With
Faktor = 1
If Monat = Monat_Start Then
If Day(StartDatum) > 1 Then
'Faktor berechnen wenn Abrechnungsbeginn nicht am Monatsersten
Jahr = Year(StartDatum)
Faktor = (DateSerial(Jahr, Monat + 1, 0) - StartDatum + 1) / _
(DateSerial(Jahr, Monat + 1, 0) - DateSerial(Jahr, Monat, 1) + 1)
End If
ElseIf Monat = Monat_Ende Then
If Month(EndeDatum + 1) = Month(EndeDatum) Then
'Faktor berechnen wenn Abrechnungsende nicht am Monatsletzten
Jahr = Year(EndeDatum)
Faktor = (EndeDatum - DateSerial(Jahr, Monat, 1) + 1) / _
(DateSerial(Jahr, Monat + 1, 0) - DateSerial(Jahr, Monat, 1) + 1)
End If
End If
.Range("F8").Offset(Monat, 0).Value = Faktor * Miete
.Range("G8").Offset(Monat, 0).Value = Faktor * NK
Next
End With
End Sub

Anzeige
AW: Codelösung gesucht oder ähnliches
05.09.2011 16:17:37
Thomas
Hallo Franz,
funktioniert super wie immer muss nun testen ob ich das Tabellenblatt "Miete_NK" so lassen kann oder nicht.
Die Formel hab ich nun auch für die Umlage nach Personen und anhand den Zahlen meines Verwalters geprüft.
Ich muss noch was anderes versuchen um den Code etwas zu verkürzen, so das ich für jede Whg nur einen Bereich habe ( Case "Whg1" ....).
Ich Dank Dir für die rasche und zuverlässige Hilfe.
Gruß Thomas

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige