PDA

View Full Version : [SOLVED] Introduce an application



jcsabi
04-14-2016, 01:03 AM
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