1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| Sub Preis()
Dim STD As Worksheet
Dim Datenbasis As Worksheet
Dim C As Range, D As Range
Dim PreisStunde As Integer
Dim Kat As String
Set STD = Worksheets("Stunden ohne Kaufl")
Set Datenbasis = Worksheets("Datenbasis")
LetzteZeile = Datenbasis.Cells(Rows.Count, 1).End(xlUp).Row
Private Function PreisStunde(Kat As String) As Integer
PreisStunde = Switch(Kat = "Z", "115", Kat = "1", "152", Kat = "2", "183", Kat = "3", "205", Kat = "4", "240", Kat = "5", "300")
End Function
For Each C In STD.Range("C4:C400")
For Each D In Datenbasis.Range("A2:A" & LetzteZeile)
If C.Value = D.Value Then
C.Offset(0, 3).Value = D.Offset(0, 3).Value
C.Offset(0, 4).Value = D.Offset(0, 1).Value
End If
Next
C.Offset(0, 5).Value = PreiStunde(C.Offset(0, 4).Value) * C.Offset(0, 6).Value
Next
End Sub |
Partager