Sub Gomb1_Kattintáskor()
Dim i, j, l, m, n, rw
'Labor nu.
Worksheets("Munkaero").Activate
For Each rw In Worksheets("Munkaero").Rows
If Cells(rw.Row, 1) = "" Then
n = rw.Row - 1
Exit For
Else
Cells(rw.Row, 7) = rw.Row
End If
Next
'Sorting
Rows("2:" & n).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("Szabadsag").Activate
Rows("2:" & n).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Calculate
j = 2
Worksheets("Eredmeny").Activate
While Cells(2, j) <> ""
'Üzemek 1-3
For i = 4 To 6
' No job on weekends etc.
If Worksheets("Tervezo").Cells(i, j) = "" Then
Cells(i, j) = "ELMARAD"
Else
'Plan
Cells(1, 1).FormulaR1C1 = "=VLOOKUP(Tervezo!R" & i & "C" & j & ",Munkaero!R2C1:R" & n & "C7,7,0)"
m = Cells(1, 1)
'Subtitute when holiday
If Worksheets("Szabadsag").Cells(m, j) = "y" Then
Cells(1, 1) = Worksheets("Munkaero").Cells(m, 5)
'Is subtitute?
If Cells(1, 1) = "Igen" Then
'Subtitute
Cells(1, 1).FormulaR1C1 = "=VLOOKUP(Munkaero!R" & m & "C6,Munkaero!R2C1:R" & n & "C7,7,0)"
m = Cells(1, 1)
l = Worksheets("Munkaero").Cells(m, 1)
'If no subtitute then CANCEL
If Worksheets("Szabadsag").Cells(m, j) = "y" Then
Cells(i, j) = "ELMARAD"
Else
If ((i = 4) And (Worksheets("Tervezo").Cells(5, j) <> l) And (Worksheets("Tervezo").Cells(6, j) <> l)) Or _
((i = 5) And (Worksheets("Tervezo").Cells(4, j) <> l) And (Worksheets("Tervezo").Cells(6, j) <> l)) Or _
((i = 6) And (Worksheets("Tervezo").Cells(4, j) <> l) And (Worksheets("Tervezo").Cells(5, j) <> l)) Then
Cells(i, j).FormulaR1C1 = "=VLOOKUP(Munkaero!R" & m & "C1,Munkaero!R2C1:R" & n & "C4,IF(R3C" & j & "=""AM"",3,IF(R3C" & j & "=""PM"",4,0)),0)"
Cells(7, j) = Cells(7, j) + Cells(i, j)
Cells(i, j) = Worksheets("Munkaero").Cells(m, 1)
Else
'No subtitute
Cells(i, j) = "ELMARAD"
End If
End If
Else
'No subtitute
Cells(i, j) = "ELMARAD"
End If
Else
'Normal case
Cells(i, j).FormulaR1C1 = "=VLOOKUP(Munkaero!R" & m & "C1,Munkaero!R2C1:R" & n & "C4,IF(R3C" & j & "=""AM"",3,IF(R3C" & j & "=""PM"",4,0)),0)"
Cells(7, j) = Cells(7, j) + Cells(i, j)
Cells(i, j) = Worksheets("Munkaero").Cells(m, 1)
End If
End If
Next i
'Summa
'Cells(7, j).FormulaR1C1 = "=SUM(R4C" & j & ":R6C" & j & ")"
Cells(1, 1) = ""
j = j + 1
Wend
End Sub