Introduce an application

[jcsabi]

Hello,

I'd like to introduce my solution for an application. The task was this:

- Worksheets are attached to the excel file in:
- Munkaero(labor): The costs and substitutions of labors
- Szabadsag(holiday): labor holiday plan
- Tervezo(design): general manager made a longer term plan for january
- Eredmeny(result): exact production plan with costs


The problem is that the designer set up a work plan (sheet Tervezo) of the
labor holiday plan (sheet Szabadsag) combined with the results (sheet Eredmeny)
fill out and calculate the cost of each shift (Uzem1, Uzem2, Uzem3) of the workforce
costs listed in AM / PM on the basis of costs


The calculation rules are as follows:


1 if the labor is no on holiday (Szabadsag sheet), you can work off the designed shift
2 if the labor on holiday, then you can replace (Labor / substitute for) the deputy will jump instead. The deputy deputy is not possible.
3 if the deputy's being worked on (when should someone be substituted) it falls short of the shift where he was to be substituted (Results:
ELMARAD/canceled)
4. Only one deputy to replace someone

The program is starting by an inserted button.

The time required to solve tasks for about an hour or two.


My solution is attached - Feladat2016.xls:
- There is no limit labor (just do not be a blank row in the list)
- There is no date limit, only agree the same dates on the Tervezo/Szabadsag/Eredmeny sheets (and do not allow them in an empty column)
- The scoreboard shows the name of the junior/subtitute and the total shifts cost

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