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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
7
Views
172
Peter_SSs
Peter_SSs
alm395
  • Question Question
Coverage By Hour
Replies
6
Views
832
Fluff
Fluff
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
364
Logit
Logit
A
  • Solved
How use RC formulas when fill three columns
2
Replies
18
Views
242
MARK858
MARK858
Allen_Mead
  • Question Question
Using EOMONTH to calculate number of hours between dates
Replies
2
Views
40
StephenCrump
S

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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