Working Hours

P

PSV86

New Member
Joined
Dec 15, 2017
Messages
8
Hello there,

I have some tricky situation, I'm creating an excel file to control some working hours during the year.

In the A column (starting the header in A3) I have the role
In the B column I have the name
In the C column I have the total hours
In the D column the sum of the working hours
In the E column the employee contract hours
From F to K have some hidden columns

First the Macro check if the employee has any kind of filled cell in other sheet and copy to the primary sheet and then

Starting in the L column (L3 header and so on) I have empty cells, and I want to create a Solver (I'm trying to create a Macro) to input some predefined numbers based on the employee contract hours (each contract has 3 variables, 3 posible numbers to fill).
This numbers needs to fill all the employee row until the totalhours (C column) is reached AND The numbers needs to fill all the column based on the total need hours (each column for every column in the L2, M2, N2, etc.)
So it needs to make 2 things based on two goals the total employee hours and the total needed week hours.

I don't know if Solver can do that

If I imput something in any cell or any cell isn't empty, the macro (or the solver) needs to respect that and doesnt erase it, only fill the empty cells.

I can paste the in-progress macro code if you need it.

The issue with my code is like the code starts filling the week from A4 and when it reaches the week goal (in L2, M2, N2, etc.) stops and start with the next column, leaving some employees without data.
And the same when the row reaches the employee max hours (in D column), stops and leave the employee with a lot of columns as 0

That's a tiny example (not completed)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]340[/TD]
[TD]230[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]200[/TD]
[TD]120[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Role[/TD]
[TD]Name[/TD]
[TD]Total Hours[/TD]
[TD]Sum Hours[/TD]
[TD]Contract[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]14[/TD]
[TD]10[/TD]
[TD]14[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]12[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]13[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]14[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]15[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]16[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]17[/TD]
[TD]92[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Every excel sheet has 64 columns and a lot of rows.
Each excel sheet is filtered by employee category (A,B,C in Sheet1, D,E,F, in Sheet2, and so on)
But each excel sheet contains the same employees (with a formula Employees!A:A)

Macro to fill A, B, C, employees:

Code: 
Sub anhours()

x = 4


Do While Range("A" & x) <> ""


rolemp = Range("A" & x)
nameemp = Range("B" & x)


If (rolemp = "A" Or rolemp = "B" Or rolemp = "C") Then


        filaHol = 3
        
        Do While Sheets("Holidays").Range("A" & filaHol) <> nameemp
                
                    filaHol = filaHol + 1
                
        Loop
        
        
        For i = 12 To 63
        
                If Cells(x, i).Value = "" Then


                    Cells(x, i).Value = Sheets("Holidays").Cells(filaHol, i - 7).Value
                
                                   
                Select Case Cells(x, i).Value
                    Case "H"
                        Cells(x, i).Interior.ColorIndex = 35
                    Case "T"
                        Cells(x, i).Interior.ColorIndex = 36
                    Case "S"
                        Cells(x, i).Interior.ColorIndex = 34


                    Case "L"
                        Cells(x, i).Interior.ColorIndex = 33


               End Select


               End If


                             
        Next i
        


End If




x = x + 1
Loop


For columna = 12 To 63


            acumuladoWeek = 0
        
            For h = 4 To 300
                If IsNumeric(Cells(h, columna).Value) Then
                    totalWeek = totalWeek + CInt(Cells(h, columna).Value)
                End If
            Next h


            weightweek = Cells(1, columna).Value
            maxhours = Cells(2, columna).Value
 
            fila = 4




             Do While (weightweek <= maxhours) And (Range("A" & fila) <> "")
             
                    rolemp = Range("A" & fila)
                    nameemp = Range("B" & fila)


                If (rolemp = "A" Or rolemp = "B" Or rolemp = "C") Then
  
                            ContrHours = Cells(fila, 5).Value
                            Select Case ContrHours
                                    Case 40
                                        f = 2
                                    Case 35
                                        f = 3
                                    Case 32
                                        f = 4
                                    Case 30
                                        f = 5
                                    Case 28
                                        f = 6
                                    Case 25
                                        f = 7
                                    Case 24
                                        f = 8
                                    Case 20
                                        f = 9
                                    Case 17.5
                                        f = 10
                                    Case 39
                                        f = 11
                                    Case 26
                                        f = 12


                            End Select
            


                            Select Case weightweek
                                    Case "P"
                                         hours = Sheets("Data").Range("BF" & f).Value
                                    Case "V"
                                         hours = Sheets("Data").Range("BG" & f).Value
                                    Case "N"
                                         hours = Sheets("Data").Range("BH" & f).Value
                             End Select
            
            
                            EmpSum = Application.WorksheetFunction.Sum(Range("L" & fila & ":BL" & fila))
            




                      If EmpSum < Range("C" & fila) Then


                                    If Cells(fila, columna).Value = "" Then
                                            Cells(fila, columna).Value = horas
                                            acumuladoWeek = acumuladoWeek + horas
                                    End If
                             
                                    If Cells(fila, columna).Value = "T" Then
                                            dosDias = Range("E" & fila).Value / 5
                                            dosDias = dosDias * 2
                                            Cells(fila, columna).Value = dosDias
                                            acumuladoWeek = acumuladoWeek + dosDias
                                    End If
                                    
                                    If Cells(fila, columna).Value = "S" Then
                                            'cuatroDias = Range("E" & fila).Value / 5
                                            'cuatroDias = cuatroDias * 4


                                            Cells(fila, columna).Value = horas
                                            acumuladoWeek = acumuladoWeek + horas
                                    End If


                                    If Cells(fila, columna).Value = "L" Then


                                            cuatroDias = Range("E" & fila).Value / 5
                                            cuatroDias = cuatroDias * 4
                                            Cells(fila, columna).Value = cuatroDias
                                            acumuladoWeek = acumuladoWeek + cuatroDias
                                    End If


                            Else
                                    If Cells(fila, columna).Value <> "H" And Cells(fila, columna).Value = "" Then
                                            Cells(fila, columna).Value = 0
                                    End If
                        End If
                        
                End If


                fila = fila + 1
             Loop


Next columna


End Sub



Thanks!!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You must log in or register to reply here.

Similar threads

H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
3
Views
100
Peter_SSs
Peter_SSs
alm395
  • Question Question
Coverage By Hour
Replies
6
Views
731
Fluff
Fluff
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
311
Logit
Logit
D
  • Solved
VBA Code: Table Column Formatting for Outlook Email
Replies
2
Views
471
Damien Hartzell
D
D
  • Solved
Error 1004
2
Replies
18
Views
352
dmt32
D

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top