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)
<tbody>
</tbody>
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:
Thanks!!
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)
| 340 | 230 | 200 | 150 | 200 | 120 | 100 | 200 | 300 | |||||
| Role | Name | Total Hours | Sum Hours | Contract | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| A | 1 | 92 | 40 | 40 | 14 | 10 | 14 | 14 | |||||
| A | 2 | 92 | 40 | 40 | |||||||||
| A | 3 | 70 | 30 | 30 | |||||||||
| B | 4 | 70 | 30 | 30 | |||||||||
| B | 5 | 92 | 40 | 40 | |||||||||
| B | 6 | 92 | 40 | 40 | |||||||||
| B | 7 | 70 | 30 | 30 | |||||||||
| B | 8 | 50 | 20 | 20 | |||||||||
| B | 9 | 50 | 20 | 20 | |||||||||
| B | 10 | 50 | 20 | 20 | |||||||||
| B | 11 | 70 | 30 | 30 | |||||||||
| C | 12 | 92 | 40 | ||||||||||
| C | 13 | 70 | 30 | ||||||||||
| C | 14 | 50 | 20 | ||||||||||
| C | 15 | 70 | 30 | ||||||||||
| C | 16 | 92 | 40 | ||||||||||
| C | 17 | 92 | 40 |
<tbody>
</tbody>
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 SubThanks!!
