VBA Add Solver constraints based on criteria

[JaCharger]

Hi,

I'm building a tool to change salaried employees hours to equal 70. I now have a way to add in a formula that adds a total hour count to be manipulated automatically, but I'm stuck on adding solver constraints automatically.

Here is the code I've been working on. It doesn't work - the

If cell.Value = "R" Then

line errors with Object Required. Not sure why.

Private Sub SalarySolver()

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
Dim pto As Range

    SolverOK SetCell:=Range("H21"), MaxMinVal:=3, ValueOf:=70, ByChange:=Range("C2:C21")

'For Each cell In Range("B2:B" & lRow)
    If cell.Value = "R" Then
    
        
    
        Else
            SolverAdd CellRef:=(2 + 1 & lRow), Relation:=2, FormulaText:="7"
            
        End If
        
    

'Next



End Sub

Essentially, all I need the code to do is look in column B, if the value is not "R" then you add the value in Column C to the constraints. These values must remain unchanged - that is one aspect of the code that I haven't implemented. I believe the SolverAdd line just sets the constraints to equal 7, when in reality they can't be changed.

Here is the mini-sheet. Any help is appreciated.

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							09/12/22
3	4638	R	3.53							09/12/22
4	4638	R	7.37							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.87							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.37		09/23/22
22	1451	R	5.12							09/12/22
23	1451	R	1.88							09/12/22
24	1451	R	3.75							09/13/22
25	1451	R	3.25							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		09/23/22
43	5257	R	0.27							09/11/22
44	5257	R	0.6							09/11/22
45	5257	R	9.52							09/12/22
46	5257	R	3.93							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.23							09/16/22
51	5257	R	3.77							09/19/22
52	5257	R	3.72							09/19/22
53	5257	R	3.8							09/20/22
54	5257	R	1.58							09/20/22
55	5257	S	7							09/21/22
56	5257	R	4.13							09/22/22
57	5257	R	1.75							09/22/22
58	5257	R	7.08					76.68		09/23/22
59	2234	R	3.65							09/12/22
60	2234	R	3.35							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		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

Cell Formulas
Range	Formula
H21,H107,H89,H78,H58,H42	H21	=SUMIF($A$2:$A$111,A21,$C$2:$C$111)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$C$2:$C$21	H107, H89, H78, H58, H42, H21
solver_lhs1	=Sheet1!$C$5:$C$7	H107, H89, H78, H58, H42, H21