AW: Formel als Makro
16.10.2016 16:05:45
Bastian
Ja das müsste auch gehen solange dein format so bleibt ´wie in der Bsp. Datei.
Hier ersetz mal war noch ein kleiner Fehler drinne
Option Explicit
Sub rechnen()
Dim ListStr
Dim PauseUeber6 As Date, PauseUeber8 As Date, Date1 As Date, Date2 As Date
Dim Jein As Long, Firstcell As Long, x As Long, c As Long
Dim WStunden As Double, MStunden As Double
Dim NamenDazu As String
Dim Ws As Worksheet
On Error GoTo err
On Error Resume Next
Dim NAmeSort As Worksheet
Dim FindenName As Range, FindenSonder As Range
Set NAmeSort = ThisWorkbook.Worksheets("NamenSonderWerte")
PauseUeber6 = "00:30:00"
PauseUeber8 = "00:45:00"
NamenDazu = "Name1,Name2,Name3" ' Hier die Namen der Worksheets eintragen die auch berechnet _
werden sollen aber nicht Januar-Dezember heißen"
For Each Ws In ThisWorkbook.Worksheets
Jein = 0
Jein = Application.WorksheetFunction.Match(CStr(Ws.Name), Split(Join(Application. _
GetCustomListContents(8), ",") & "," & NamenDazu, ","), 0)
'Jein = InStr(1, Join(Application.GetCustomListContents(8), ",") & NamenDazu, Ws.Name)
If Not Jein = 0 Then
With Ws
Firstcell = 7
Do While Not IsEmpty(.Cells(Firstcell + x, 1))
For c = 3 To 16 Step 2
If IsNumeric(.Cells(Firstcell + x, c).Value) Then
Date1 = CDate(.Cells(Firstcell + x, c).Value)
Date2 = CDate(.Cells(Firstcell + x, c).Offset(0, 1).Value)
If (Date2 - Date1) > CDate("08:00:00") Then
WStunden = WStunden + (Date2 - Date1 - (1 * CDate(PauseUeber8)))
ElseIf (Date2 - Date1) > CDate("06:00:00") Then
WStunden = WStunden + (Date2 - Date1 - (1 * CDate(PauseUeber6)))
Else
WStunden = WStunden + (Date2 - Date1)
End If
Else
Set FindenName = NAmeSort.Columns(1).Find(.Cells(Firstcell + x, 1).Value, _
LookIn:=xlValues)
Set FindenSonder = NAmeSort.Rows("1:1").Find(.Cells(Firstcell + x, c).Value, _
LookIn:=xlValues)
Select Case UCase(.Cells(Firstcell + x, c).Value)
Case "K"
WStunden = WStunden + CDate(NAmeSort.Cells(FindenName.Row, FindenSonder.Column). _
Value)
Case "FT"
WStunden = WStunden + CDate(NAmeSort.Cells(FindenName.Row, FindenSonder.Column) _
.Value)
Case "K"
WStunden = WStunden + CDate(NAmeSort.Cells(FindenName.Row, FindenSonder.Column) _
.Value)
End Select
End If
Next
With .Range("R" & Firstcell + x)
.Value = WStunden
.NumberFormat = "[h]:mm:ss;@"
End With
MStunden = MStunden + WStunden
WStunden = 0
x = x + 4
Loop
With .Range("S" & Firstcell)
.Value = MStunden
.NumberFormat = "[h]:mm:ss;@"
End With
End With
MStunden = 0
x = 0
End If
Next
Exit Sub
err:
MsgBox "In der Tabelle " & NAmeSort.Name & " kann der Name einer Person nicht gefunden _
werden "
End Sub
Gruß Basti