Zellen Addieren

[Kaywarri124]

Guten Morgen meine Excel Fans,

Ich habe eine wunderschöne Idee und somit eine Problem.

Aufgabe:  Ich habe eine Arbeitszeittabelle

     A              B             C           D           E           F          G          H                          P                     Q
1 Name   l Vorname l Gesetzt l Januar  l Februar  l  März  l   Mai   l   Juni   l... usw. l  Überstunden l  Anzahl halbe Tage
2 Name1
3 Name1
4 Name2
5 Name2
6 Name3
7 Name3
...
usw.

Nun soll in Zeile 2, für jeden Mitarbeiter die geleisteten Mehrstunden rein geschrieben werden ( z.B.: 35:00h ), das passiert ab "D2"

Alles über 35:00h sind Mehrstunden und müssen in den nächsten Monat übernommen werden.

Was soll passieren:

Bei Eingabe in "D2" z.B. 70:00:00 > ( Das ist was ich schon habe )

Zelle "Q2"

=WENN(D2>=ABS("35:00");1;0)+WENN(F2>=ABS("35:00");1;0)+WENN(H2>=ABS("35:00");1;0)+WENN(J2>=ABS("35:00");1;0)+WENN(L2>=ABS("35:00");1;0)+WENN(N2>=ABS("35:00");1;0)+WENN(P2>=ABS("35:00");1;0)+WENN(R2>=ABS("35:00");1;0)+WENN(T2>=ABS("35:00");1;0)+WENN(V2>=ABS("35:00");1;0)+WENN(X2>=ABS("35:00");1;0)+WENN(Z2>=ABS("35:00");1;0)

[ Da 35h pro Monat = ein halber Tag und man bekommt nur einen halben Tag pro Monat ]

Wenn Zelle "D2" 70:00h steht, dann steht in "Q2"=1 und die 35:00h Rest müssen dann in den nächsten Monat.

Wenn ich dann in Zelle "E2" die Mehrstunden X aus dem Monate Februar schreibe, kommt die Summe X+35:00H Rest aus dem Vormonat.

Das habe ich bis jetzt so gelöst:

Sub Worksheet_Change(ByVal Target As Range)
 Dim a
 Dim zelle As Range
 Dim Bereich As Range
 

'***********************************************************
'Januar
'***********************************************************
 a = ActiveSheet.Range("E1").Value
 If Not Intersect(Target, Me.Range("F2:F2")) Is Nothing Then
 Application.EnableEvents = False
 Target.Value = a + Target.Value
 End If
 Application.EnableEvents = True
 If Not Intersect(Target, Me.Range("E1:E1")) Is Nothing Then
 Application.EnableEvents = False
 Set Bereich = Range("F2:F2")
 For Each zelle In Bereich
 If zelle.Value <> "" Then
 zelle.Value = zelle.Value + Range("E1").Value
 End If
 Next
 End If
 Application.EnableEvents = True
'***********************************************************
'Februar
'***********************************************************
 a = ActiveSheet.Range("G1").Value
 If Not Intersect(Target, Me.Range("H2:H2")) Is Nothing Then
 Application.EnableEvents = False
 Target.Value = a + Target.Value
 End If
 Application.EnableEvents = True
 If Not Intersect(Target, Me.Range("G1:G1")) Is Nothing Then
 Application.EnableEvents = False
 Set Bereich = Range("H2:H2")
 For Each zelle In Bereich
 If zelle.Value <> "" Then
 zelle.Value = zelle.Value + Range("G1").Value
 End If
 Next
 End If
 Application.EnableEvents = True
....
usw. für jeden Monat


Jetzt muss ich aber dafür zusätzlich eine Spalte und eine Zeile einfügen und das umständlich für jeden Monate und jeden neuen Mitarbeiter in VBA kopieren und umschreiben.

Mir würde eine Lösung mit der ich einfach den Wert addiere schon reichen, Hauptsache ich muss nicht den VBA-Code kopieren und umschreiben

So sieht meine Tabelle mit dem VBA aus

     A              B             C           D           E           F          G             H          I            J       K              L                           P                     Q
1 Name   l Vorname l Gesetzt l Januar  l Zusatz  l Februar l Zusatz l  März  l Zusatz   l   Mai  l Zusatz   l   Juni   l... usw. l  Überstunden l  Anzahl halbe Tage
2 Name1
3 Name1
4 Zusatz
5 Name2
6 Name2
7 Zusatz
8 Name3
9 Name3

In der Zusätzlichen Zellen steht =WENN(D2>ABS("35:00");D2-"35:00";0)


Schon mal VIIIIIIIEEEELEN Dank im voraus :05: :05: :05:

[Matthias84]

Hallo,

warum hast du überhaupt für jeden Monat einen eigenen Code? Über eine Schleife (vgl. z.B. [url=
Dateiupload bitte im Forum! So geht es: Klick mich!
9)[/url] würde dir ein Code reichen, den du nur einmalig anpassen musst.

Irgendwie kann ich auch deine Erklärung nicht so ganz nachvollziehen - am besten lädst du mal deine (anonymisierte) Datei hoch.

[Elex]

Hallo

Mal als Ansatz.

____|__D__|__E__|__F__|__G__|__H__|__I_|__J__|__K__|__L__|__M_|__N__|__O_|_Q_|
   1|Jan  |Feb  |Mrz  |Apr  |Mai  |Jun |Jul  |Aug  |Sep  |Okt |Nov  |Dez |H T|
   2|70:00|10:00| 0:00| 0:00|15:00|1:00|25:00|10:00| 0:00|0:00|15:00|1:00|  4|
   3|20:00| 0:00|10:00|10:00| 0:00|0:00|15:00| 1:00|25:00|0:00|15:00|1:00|  2|
   4| 0:00|35:00| 0:00| 0:00|15:00|0:00| 0:00|20:00| 0:00|0:00|35:00|0:00|  3|

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Stunden, SthT As Double
Dim hTage, i, n As Long
Dim Werte As Variant

Werte = Range("D2:O" & Cells(Rows.Count, 1).End(xlUp).Row).Value
SthT = 35 / 24

For i = 1 To UBound(Werte, 1)
  hTage = 0
  Stunden = 0
    For n = 1 To 12
      Stunden = Stunden + Werte(i, n)
       If Stunden - SthT >= 0 Then
          Stunden = Stunden - SthT
          hTage = hTage + 1
       End If
    Next n
Range("Q" & 1 + i).Value = hTage
Next i
Cancel = True
End Sub

Gruß Elex

[Kaywarri124]

  Also mit Schleifen kenn ich mich nicht aus, habe es mir mal angeguckt vielleicht geht es.

Aber habe deshalb jeden Monat einen Code, da der Code sich nur auf 2 bestimmte Zellen bezieht und sich meine ständig ändern.



Und danke Elex, sieht interessant aus, nur das immer 1 zählt wenn 35h erreicht sind, aber er darf nur einmal pro Monat 1 zählen.

[Elex]

Hi

Bitte genauer Beschreiben.

Was ist an dem Ergebnis in Spalte Q nicht richtig?
Was ist wenn 20h in den nächsten Monat gehen und der Monat hat 17h = Gesamt 37h. Dann sollen 35h weg und zwei in den nächsten Monat. Oder nicht?

Ab D2 werden nur die Stunden in jeweiligen Monat eingetragen. im Jan sind es = 70h und im Feb sind es = 10h.


Gruß Elex

[Kaywarri124]

Huhu wieder ein schönes Beispiel für Sender und Empfänger, aber wir bekommen das schon hin ^^


Januar   45h  =  1d     Rest = 10h

Februar 40h + 10h ( aus Januar ) = 50 h = 1d    Rest = 15h

März   10h + 15h ( aus Februar ) = 25h = 0d    Rest = 25h ( alles über 5h und unter 35h bleibt, gibt aber keinen Tag )

April 25h + 25h ( aus März ) = 50h =  1d    Rest = 15h

Mai 65h + 15h ( aus April ) = 80h = 1d    Rest = 45h  ( maximal nur 1d pro Monat und max 12d pro Jahr )

Juni 0h + 45h ( aus Mai ) = 45h = 1d   Rest = 5h

Juli 0h + 5h ( aus Juni ) = 5h = 0d    Rest = 0h  ( alles <=5h verfällt )

[Elex]

Hi

Und noch mal meine Frage. Was stimmt am bisherrigen Ergebnis in Spalte Q nicht.

Habe den Code um die bisher unbekannte (kleiner 5h Verfall) ergänzt.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Stunden, SthT, StVf As Double
Dim hTage, i, n As Long
Dim Werte As Variant

Werte = Range("D2:O" & Cells(Rows.Count, 1).End(xlUp).Row).Value
SthT = 35 / 24
StVf = 5 / 24
For i = 1 To UBound(Werte, 1)
 hTage = 0
 Stunden = 0
   For n = 1 To 12
     Stunden = Stunden + Werte(i, n)
      If Stunden <= StVf Then Stunden = 0
       If Stunden - SthT >= 0 Then
         Stunden = Stunden - SthT
         hTage = hTage + 1
       End If
      Next n
Range("Q" & 1 + i).Value = hTage
Next i
Cancel = True
End Sub

Gruß Elex

[Kaywarri124]

Also das klappt schon mal ganz gut,

aber wenn ich jetzt Doppel klicke habe ich bei

Jan           Feb          März         April                         Tage
70:00:00   35:00:00  35:00:00   140:00:00                  ist 8 soll 4


Ich kann über 35h kommen, aber ist trotzdem nur ein Tag und der Rest ( alles über 35 ) soll auf den nächsten Monat raufgerechnet werden.
Alles >= 35 gibt einen Tag und der Rest geht in den nächsten Monat
alles < 35 kommt in nächsten Monat
alles <=5 verfällt
alles > 105 verfällt auch


Abs.2 ist erstmal zu ignorieren

[Elex]

Moin

Nach bisherigen Angaben sind es 8d. Denn es ist egal was du im Mai – August einträgst.
 
Januar  70h = 1d   Rest = 35h
Februar 35h + 35h (Rest Januar) = 70h   = 1d  Rest(35h)
März  35h + 35h (Rest Februarr) = 70h   = 1d  Rest(35h)
April  140h + 35h (Rest März) = 175h   = 1d  Rest(140h)
Mai egal + 140h (Rest April) = 140h   = 1d  Rest(105h)
Juni egal + 105h (Rest Mai) = 105h   = 1d  Rest(70h)
Juli egal + 70h (Rest Juni) = 70h   = 1d  Rest(35h)
August egal + 35h (Rest Juli) = 35h   = 1d  Rest(0h)
 
Wenn du jetzt aber meinst du möchtest die Berechnung nur bis zu den Zellen in denen auch eine Eingabe steht (in dem Fall April) dann so. Die neue Bedingung Verfall > 105 und Reststunden in R sind auch drin.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Stunden, SthT, StVf, StGr As Double
Dim hTage, i, n As Long
Dim Werte As Variant

Werte = Range("D1:O" & Cells(Rows.Count, 1).End(xlUp).Row).Value
SthT = 35 / 24
StVf = 5.01 / 24
StGr = 105 / 24
For i = 2 To UBound(Werte, 1) Step 2
  hTage = 0
  Stunden = 0
    For n = 1 To 12
      If Werte(i, n) = "" Then Exit For
      Stunden = Stunden + Werte(i, n)
      If Stunden <= StVf Then Stunden = 0
        If Stunden - SthT >= 0 Then
          Stunden = Stunden - SthT
          If Stunden > StGr Then Stunden = StGr
          hTage = hTage + 1
        End If
    Next n
Range("Q" & i).Value = hTage
Range("R" & i).Value = Stunden
Next i
Cancel = True
End Sub

Beachte dass es jetzt zwischendurch keine Leerzellen geben darf.
 
   1|Jan  |Feb  |Mrz  |Apr  |Mai  |
   2|70:00|10:00| 0:00| 0:00|15:00|  richtig
   3|20:00| 0:00|     |10:00| 0:00|  falsch

Gruß Elex

[Kaywarri124]

Guten Morgen und schönen Wochenstart,

Elex einfach nur krass... es funktioniert richtig gut. Keine Ahnung, habe wohl was falsch verstanden.

Aber dein zweiter Code passt (fast) perfekt.  :05: :05:

Vielleicht geht es ja noch perfekter, dann geb ich nen Essen aus :D:D

Habe mal ein Beispiel gegeben.

Die Summe der Stunden geht immer weiter runter.

IST        Januar = 130h = 1d = 95h Rest

SOLL      Januar = 130h = 105h = 1d = 70h Rest

Wenn man das Spiel weiter macht, dann kommt am ende ein Tag mehr raus. :22:


Aber wenn das nicht geht dann ist das halt so. Aber bis dahin ist es echt der hammer.