Office: Optimierung einer Formel für eine Arbeitserfassung

Okay :)


Lösung des Rätsels: Wenn die Spalte zu eng ist, rundet Excel die Zahl einfach auf...

Sehr interessant :)

 

Hallo Leute,

ich habe nun per VBA einen Code gebastelt, um in mehreren Tabellenblättern Zelleninhalte zu löschen. Der Code funktioniert auch super, aber ich denke mir, der ist bestimmt noch verbesserungsfähig. Da ich mich nicht so gut auskenne mit VBA dachte ich, ihr könnt euch das evtl. mal anschauen und mir Tipps geben :)

Code:

Private Sub CommandButton10_Click()
Sheets("Jan").Select
Sheets("Jan").Unprotect Password:="test"
Sheets("Jan").Range("C10:D40").ClearContents
Sheets("Jan").Range("J10:J40").ClearContents
Sheets("Jan").Range("C60:J90").ClearContents
Sheets("Jan").Range("E48:E48").ClearContents
Sheets("Jan").Range("E95:E95").ClearContents
Sheets("Jan").Protect Password:="test"
Sheets("Feb").Select
Sheets("Feb").Unprotect Password:="test"
Sheets("Feb").Range("C10:D40").ClearContents
Sheets("Feb").Range("J10:J40").ClearContents
Sheets("Feb").Range("C60:J90").ClearContents
Sheets("Feb").Range("E48:E48").ClearContents
Sheets("Feb").Range("E95:E95").ClearContents
Sheets("Feb").Protect Password:="test"
Sheets("Mrz").Select
Sheets("Mrz").Unprotect Password:="test"
Sheets("Mrz").Range("C10:D40").ClearContents
Sheets("Mrz").Range("J10:J40").ClearContents
Sheets("Mrz").Range("C60:J90").ClearContents
Sheets("Mrz").Range("E48:E48").ClearContents
Sheets("Mrz").Range("E95:E95").ClearContents
Sheets("Mrz").Protect Password:="test"
Sheets("Apr").Select
Sheets("Apr").Unprotect Password:="test"
Sheets("Apr").Range("C10:D40").ClearContents
Sheets("Apr").Range("J10:J40").ClearContents
Sheets("Apr").Range("C60:J90").ClearContents
Sheets("Apr").Range("E48:E48").ClearContents
Sheets("Apr").Range("E95:E95").ClearContents
Sheets("Apr").Protect Password:="test"
Sheets("Mai").Select
Sheets("Mai").Unprotect Password:="test"
Sheets("Mai").Range("C10:D40").ClearContents
Sheets("Mai").Range("J10:J40").ClearContents
Sheets("Mai").Range("C60:J90").ClearContents
Sheets("Mai").Range("E48:E48").ClearContents
Sheets("Mai").Range("E95:E95").ClearContents
Sheets("Mai").Protect Password:="test"
Sheets("Jun").Select
Sheets("Jun").Unprotect Password:="test"
Sheets("Jun").Range("C10:D40").ClearContents
Sheets("Jun").Range("J10:J40").ClearContents
Sheets("Jun").Range("C60:J90").ClearContents
Sheets("Jun").Range("E48:E48").ClearContents
Sheets("Jun").Range("E95:E95").ClearContents
Sheets("Jun").Protect Password:="test"
Sheets("Jul").Select
Sheets("Jul").Unprotect Password:="test"
Sheets("Jul").Range("C10:D40").ClearContents
Sheets("Jul").Range("J10:J40").ClearContents
Sheets("Jul").Range("C60:J90").ClearContents
Sheets("Jul").Range("E48:E48").ClearContents
Sheets("Jul").Range("E95:E95").ClearContents
Sheets("Jul").Protect Password:="test"
Sheets("Aug").Select
Sheets("Aug").Unprotect Password:="test"
Sheets("Aug").Range("C10:D40").ClearContents
Sheets("Aug").Range("J10:J40").ClearContents
Sheets("Aug").Range("C60:J90").ClearContents
Sheets("Aug").Range("E48:E48").ClearContents
Sheets("Aug").Range("E95:E95").ClearContents
Sheets("Aug").Protect Password:="test"
Sheets("Sep").Select
Sheets("Sep").Unprotect Password:="test"
Sheets("Sep").Range("C10:D40").ClearContents
Sheets("Sep").Range("J10:J40").ClearContents
Sheets("Sep").Range("C60:J90").ClearContents
Sheets("Sep").Range("E48:E48").ClearContents
Sheets("Sep").Range("E95:E95").ClearContents
Sheets("Sep").Protect Password:="test"
Sheets("Okt").Select
Sheets("Okt").Unprotect Password:="test"
Sheets("Okt").Range("C10:D40").ClearContents
Sheets("Okt").Range("J10:J40").ClearContents
Sheets("Okt").Range("C60:J90").ClearContents
Sheets("Okt").Range("E48:E48").ClearContents
Sheets("Okt").Range("E95:E95").ClearContents
Sheets("Okt").Protect Password:="test"
Sheets("Nov").Select
Sheets("Nov").Unprotect Password:="test"
Sheets("Nov").Range("C10:D40").ClearContents
Sheets("Nov").Range("J10:J40").ClearContents
Sheets("Nov").Range("C60:J90").ClearContents
Sheets("Nov").Range("E48:E48").ClearContents
Sheets("Nov").Range("E95:E95").ClearContents
Sheets("Nov").Protect Password:="test"
Sheets("Dez").Select
Sheets("Dez").Unprotect Password:="test"
Sheets("Dez").Range("C10:D40").ClearContents
Sheets("Dez").Range("J10:J40").ClearContents
Sheets("Dez").Range("C60:J90").ClearContents
Sheets("Dez").Range("E48:E48").ClearContents
Sheets("Dez").Range("E95:E95").ClearContents
Sheets("Dez").Protect Password:="test"
Sheets("Urlaubsübersicht").Select
Sheets("Urlaubsübersicht").Unprotect Password:="test"
Sheets("Urlaubsübersicht").Range("P8:R22").ClearContents
Sheets("Urlaubsübersicht").Protect Password:="test"
Sheets("Stammdaten").Select
Sheets("Stammdaten").Range("C2:D3").ClearContents
Sheets("Stammdaten").Range("C5:C8").ClearContents
MsgBox "Nun sind ALLE Daten gelöscht!"
End Sub

 

Hallo,

folgender Code müsste funktionieren - allerdings mit einer möglichen kleinen Einschränkung:
Ich habe für die Arbeitsblätter mit den Monatsnamen die Funktion "MonthName(M,True)" verwendet: diese Funktion produziert jedoch unterschiedliche Abkürzungen aus je 3 Buchstaben, je nachdem auf welches Sprachgebiet Excel eingestellt ist. So erhält man zB.
für de-DE (=deutsch/Deutschland): "Jan", "Feb", "Mär", ..., "Dez"
für de-AT (=deutsch/Österreich): "Jän", "Feb", "Mär", ...., "Dez"
Die Monatsarbeitsblätter müssten also exakt die Namen des eingestellten Sprachgebietes haben.

Code:

Private Sub CommandButton10_Click()
  Dim M As Integer
  
  For M = 1 To 12
    BlattBereicheLoeschen MonthName(M, True), "test", "C10:D40, J10:J40, C60:J90, E48, E95"
  Next M
  BlattBereicheLoeschen "Urlaubsübersicht", "test", "P8:R22"
  BlattBereicheLoeschen "Stammdaten", "", "C2:D3, C5:C8"
  
  MsgBox "Nun sind ALLE Daten gelöscht!"
End Sub
Private Sub BlattBereicheLoeschen(BlattBez$, PWD$, Bereiche$)
  With Sheets(BlattBez$)
    If Len(PWD$) Then .Unprotect Password:=PWD$
    .Range(Bereiche$).ClearContents
    If Len(PWD$) Then .Protect Password:=PWD$
  End With
End Sub

Sollte dies jedoch Probleme verursachen, müsste man folgende Variante nehmen:

Code:

Private Sub CommandButton10_Click()
  Dim M As Integer
  Dim Monate As Variant
  
  Monate = Array("", "Jan", "Feb", "Mär", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez")
  
  For M = 1 To 12
    BlattBereicheLoeschen (Monate(M)), "test", "C10:D40, J10:J40, C60:J90, E48, E95"
  Next M
  BlattBereicheLoeschen "Urlaubsübersicht", "test", "P8:R22"
  BlattBereicheLoeschen "Stammdaten", "", "C2:D3, C5:C8"
  
  MsgBox "Nun sind ALLE Daten gelöscht!"
End Sub
Private Sub BlattBereicheLoeschen(BlattBez$, PWD$, Bereiche$)
  With Sheets(BlattBez$)
    If Len(PWD$) Then .Unprotect Password:=PWD$
    .Range(Bereiche$).ClearContents
    If Len(PWD$) Then .Protect Password:=PWD$
  End With
End Sub

Anmerkung: Die Schreibweise "(Monate(M))" mit der Klammerung darf nicht geändert werden, sonst erhält man einen Übergabefehler an die SUB "BlattBereicheLoeschen".

 

Super :) danke dir vielmals, ich habe mich für Variante 2 entschieden.

 

Noch eine Problematik:

Bisher berechne ich mit folgender Formel die geleistete Arbeitszeit:

Code:

=WENN(UND(C10="";D10="");D10-C10;WENN(ODER(A10="Sa";A10="So";ZÄHLENWENN(Inhalt8h;J10)>0;B10="");0;D10-C10-"0:30"))

Das Problem hierbei ist, das IMMER 30 Minuten Pause abgezogen werden, es sei denn, Zelle C10 UND D10 sind leer.

Nun habe ich mir überlegt, ich erweitere die Formel noch um die Abfrage, ob die Arbeitszeit <= 4 Stunden ist, und zwar wie folgt:

Code:

=WENN(ODER(D10-C10<=4;UND(C10="";D10=""));D10-C10;WENN(ODER(A10="Sa";A10="So";ZÄHLENWENN(Inhalt8h;J10)>0;B10="");0;D10-C10-"0:30"))

Aber das Problem hier ist, das er nun immer die 30 Minuten Pause ignoriert. Habe ich die Abfrage D10-C10<=4 falsch definiert?

 

Hallo,

ich möchte kurz zusammenfassen, was du möchtest, um sicher zu sein in dem, was du willst:
Zusätzlich zur 1. Variante (=falls C10 und D10 beide leer sind) möchtest du auch dann von der Differenz D10-C10 keine 1/2 Stunde abziehen, wenn diese Differenz kleiner/gleich 4 Stunden ist.

Generell gilt in den Office-Programmen: die Datum/Zeit-Einheit ist stets: 1 Tag.
Somit sind 4 Stunden = 4/24 Tage = 1/6 Tag

Aus diesem Grund ist deine Formel folgendermaßen zu modifizieren (rot gekennzeichnet):

Code:

=WENN(ODER(D10-C10<=4[COLOR="#FF0000"]/24[/COLOR];UND(C10="";D10=""));D10-C10;WENN(ODER(A10="Sa";A10="So";ZÄHLENWENN(Inhalt8h;J10)>0;B10="");0;D10-C10-"0:30"))

Also lagst du mit deiner Vermutung, dass es an "D10-C10<=4" liegt, goldrichtig.

 

Achsoooooo :D okay, dann war mein Gedankengang schon richtig, hab das nur etwas falsch angegangen :)

Danke dir :)