Hi,
I'm working on building a script to modify a time tracking sheet to fit what we need as far as formatting. I'm stuck on this last part and that is changing salaried people's time to equal 70 hours. Our old software automatically did it, but the one we are using now does not.
I've settled on using Solver to make this work. The issue is, some code that I found (that will automatically add up times based on if values in column 1 match) only places the hard value into the sheet, rather than a sum formula - what I need for Solver to work.
Here is the modified code that allows this to happen.
And here is the mini-sheet.
The totals are placed in the H column temporarily. (Also, why does it paste 3 times for the first employee? Not that it matters...)
I would like to have the total values be a formula that solver can change, if possible.
Thank you
I'm working on building a script to modify a time tracking sheet to fit what we need as far as formatting. I'm stuck on this last part and that is changing salaried people's time to equal 70 hours. Our old software automatically did it, but the one we are using now does not.
I've settled on using Solver to make this work. The issue is, some code that I found (that will automatically add up times based on if values in column 1 match) only places the hard value into the sheet, rather than a sum formula - what I need for Solver to work.
Here is the modified code that allows this to happen.
VBA Code:
Private Sub SalaryTotals()
Dim wb As Workbook, og As Workbook
Dim ws1 As Worksheet
Dim lRow As Long, i As Long, endRow As Long
Dim CopyRange As String, PasteRange As String, searchValue As String
Set ws1 = Worksheets("Sheet1")
Set os = Worksheets("Sheet1")
lRow = os.Cells(Rows.Count, 1).End(xlUp).Row
With ws1.Range("H2:H" & lRow + 4)
.ClearContents
.Value = ws1.Evaluate("INDEX(SUMIF(A2:A" & lRow + 4 & ",A2:A" & lRow + 4 & ",C2:C" & lRow + 4 & "),)")
End With
i = 5
For i = 5 To lRow + 4
If ws1.Cells(i, 7).Value = ws1.Cells(i, 8).Value Then
ws1.Cells(i, 8).Value = ""
End If
Next i
i = 5
For i = 5 To lRow + 4
If ws1.Cells(i, 8).Value = ws1.Cells(i + 1, 8).Value Then
ws1.Cells(i, 8).Value = ""
End If
Next i
Let PasteRange = "H2:H" & lRow + 4
End Sub
And here is the mini-sheet.
VIP Processing Tool.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | EmpNo | PaycodeCode | Hours | Rate | Flat Amount | Salary | First Ignored | Second Ignored | Shift Diff | WorkDate | ||
2 | 4638 | R | 3.8 | 76.4 | 09/12/22 | |||||||
3 | 4638 | R | 3.5 | 76.4 | 09/12/22 | |||||||
4 | 4638 | R | 7.4 | 76.4 | 09/13/22 | |||||||
5 | 4638 | V | 7 | 09/14/22 | ||||||||
6 | 4638 | V | 7 | 09/15/22 | ||||||||
7 | 4638 | V | 7 | 09/16/22 | ||||||||
8 | 4638 | R | 3.8 | 09/19/22 | ||||||||
9 | 4638 | R | 3.5 | 09/19/22 | ||||||||
10 | 4638 | R | 1 | 09/19/22 | ||||||||
11 | 4638 | R | 3.9 | 09/20/22 | ||||||||
12 | 4638 | R | 3.6 | 09/20/22 | ||||||||
13 | 4638 | R | 1 | 09/21/22 | ||||||||
14 | 4638 | R | 3.8 | 09/21/22 | ||||||||
15 | 4638 | R | 3.5 | 09/21/22 | ||||||||
16 | 4638 | R | 1 | 09/22/22 | ||||||||
17 | 4638 | R | 3.8 | 09/22/22 | ||||||||
18 | 4638 | R | 3.5 | 09/22/22 | ||||||||
19 | 4638 | R | 1 | 09/23/22 | ||||||||
20 | 4638 | R | 3.8 | 09/23/22 | ||||||||
21 | 4638 | R | 3.5 | 76.4 | 09/23/22 | |||||||
22 | 1451 | R | 5.1 | 09/12/22 | ||||||||
23 | 1451 | R | 1.9 | 09/12/22 | ||||||||
24 | 1451 | R | 3.8 | 09/13/22 | ||||||||
25 | 1451 | R | 3.3 | 09/13/22 | ||||||||
26 | 1451 | R | 6 | 09/14/22 | ||||||||
27 | 1451 | R | 4 | 09/15/22 | ||||||||
28 | 1451 | R | 3 | 09/15/22 | ||||||||
29 | 1451 | R | 2 | 09/16/22 | ||||||||
30 | 1451 | R | 1.6 | 09/16/22 | ||||||||
31 | 1451 | R | 1.5 | 09/16/22 | ||||||||
32 | 1451 | R | 3.5 | 09/16/22 | ||||||||
33 | 1451 | R | 0.9 | 09/16/22 | ||||||||
34 | 1451 | R | 4.5 | 09/19/22 | ||||||||
35 | 1451 | R | 2.5 | 09/19/22 | ||||||||
36 | 1451 | R | 3.8 | 09/20/22 | ||||||||
37 | 1451 | R | 3.2 | 09/20/22 | ||||||||
38 | 1451 | R | 3.5 | 09/21/22 | ||||||||
39 | 1451 | R | 3.5 | 09/21/22 | ||||||||
40 | 1451 | R | 4.5 | 09/22/22 | ||||||||
41 | 1451 | R | 2.5 | 09/22/22 | ||||||||
42 | 1451 | V | 5.5 | 70.1 | 09/23/22 | |||||||
43 | 5257 | R | 0.3 | 09/11/22 | ||||||||
44 | 5257 | R | 0.6 | 09/11/22 | ||||||||
45 | 5257 | R | 9.5 | 09/12/22 | ||||||||
46 | 5257 | R | 3.9 | 09/13/22 | ||||||||
47 | 5257 | R | 3.4 | 09/13/22 | ||||||||
48 | 5257 | R | 8.5 | 09/14/22 | ||||||||
49 | 5257 | R | 10.4 | 09/15/22 | ||||||||
50 | 5257 | R | 7.2 | 09/16/22 | ||||||||
51 | 5257 | R | 3.8 | 09/19/22 | ||||||||
52 | 5257 | R | 3.7 | 09/19/22 | ||||||||
53 | 5257 | R | 3.8 | 09/20/22 | ||||||||
54 | 5257 | R | 1.6 | 09/20/22 | ||||||||
55 | 5257 | S | 7 | 09/21/22 | ||||||||
56 | 5257 | R | 4.1 | 09/22/22 | ||||||||
57 | 5257 | R | 1.8 | 09/22/22 | ||||||||
58 | 5257 | R | 7.1 | 76.7 | 09/23/22 | |||||||
59 | 2234 | R | 3.7 | 09/12/22 | ||||||||
60 | 2234 | R | 3.4 | 09/12/22 | ||||||||
61 | 2234 | R | 3.5 | 09/13/22 | ||||||||
62 | 2234 | R | 3.5 | 09/13/22 | ||||||||
63 | 2234 | R | 3.5 | 09/14/22 | ||||||||
64 | 2234 | R | 3.5 | 09/14/22 | ||||||||
65 | 2234 | R | 3.5 | 09/15/22 | ||||||||
66 | 2234 | R | 3.5 | 09/15/22 | ||||||||
67 | 2234 | R | 3.5 | 09/16/22 | ||||||||
68 | 2234 | R | 3.5 | 09/16/22 | ||||||||
69 | 2234 | R | 3.5 | 09/19/22 | ||||||||
70 | 2234 | R | 3.5 | 09/19/22 | ||||||||
71 | 2234 | R | 3.5 | 09/20/22 | ||||||||
72 | 2234 | R | 3.5 | 09/20/22 | ||||||||
73 | 2234 | R | 3.5 | 09/21/22 | ||||||||
74 | 2234 | R | 3.5 | 09/21/22 | ||||||||
75 | 2234 | R | 3.5 | 09/22/22 | ||||||||
76 | 2234 | R | 3.5 | 09/22/22 | ||||||||
77 | 2234 | R | 3.5 | 09/23/22 | ||||||||
78 | 2234 | R | 3.5 | 70.1 | 09/23/22 | |||||||
79 | 1234 | R | 3.5 | 09/12/22 | ||||||||
80 | 1234 | PTO | 7 | 09/12/22 | ||||||||
81 | 1234 | S | 7 | 09/13/22 | ||||||||
82 | 1234 | P | 7 | 09/14/22 | ||||||||
83 | 1234 | C | 7 | 09/15/22 | ||||||||
84 | 1234 | AL | 7 | 09/16/22 | ||||||||
85 | 1234 | CCP | 7 | 09/19/22 | ||||||||
86 | 1234 | SDU | 7 | 09/20/22 | ||||||||
87 | 1234 | FH | 7 | 09/21/22 | ||||||||
88 | 1234 | FS | 14 | 09/22/22 | ||||||||
89 | 1234 | FV | 7 | 80.5 | 09/23/22 | |||||||
90 | 2899 | R | 2.4 | 09/12/22 | ||||||||
91 | 2899 | R | 4.8 | 09/12/22 | ||||||||
92 | 2899 | R | 4.1 | 09/13/22 | ||||||||
93 | 2899 | R | 3.2 | 09/13/22 | ||||||||
94 | 2899 | R | 2.2 | 09/14/22 | ||||||||
95 | 2899 | R | 5.2 | 09/14/22 | ||||||||
96 | 2899 | R | 4.3 | 09/15/22 | ||||||||
97 | 2899 | R | 3.5 | 09/15/22 | ||||||||
98 | 2899 | R | 4.1 | 09/16/22 | ||||||||
99 | 2899 | R | 5.3 | 09/16/22 | ||||||||
100 | 2899 | R | 2.3 | 09/19/22 | ||||||||
101 | 2899 | R | 4.4 | 09/19/22 | ||||||||
102 | 2899 | R | 4.2 | 09/20/22 | ||||||||
103 | 2899 | R | 3.2 | 09/20/22 | ||||||||
104 | 2899 | R | 2.3 | 09/21/22 | ||||||||
105 | 2899 | R | 4.7 | 09/21/22 | ||||||||
106 | 2899 | R | 2.5 | 09/22/22 | ||||||||
107 | 2899 | R | 4.8 | 67.5 | 09/22/22 | |||||||
Sheet1 |
The totals are placed in the H column temporarily. (Also, why does it paste 3 times for the first employee? Not that it matters...)
I would like to have the total values be a formula that solver can change, if possible.
Thank you
Last edited by a moderator: