Automating Solver Objective Selection

[JaCharger]

Hi,

I'm working on a script to automate using Solver. One of the scripts I'm working on is this:

Private Sub SolverSolver()
Dim ws1 As Worksheet
Dim lRow As Long
Dim cell As Range

Set ws1 = Worksheets("Sheet1")
ws1.Activate
lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    ws1.Range("H2:H4").ClearContents
    
    For Each cell In ws1.Range("H2:H" & Range("H2").End(xlDown).Row)
        If cell.HasFormula = True Then
            SolverOK setCell:=cell, _
                MaxMinVal:=3, _
                ValueOf:="70", _
                byChange:=Range("C2:C" & Range("C2").End(xlDown).Row)
            SolverSolve (True)
            End If
    Next cell
    
    
End Sub

This only makes the first cell that contains a formula into a reference of itself. For example, H21 being the first cell to have a formula in a sample spreadsheet (using =SUMIF) become $H$21. No other cell in the sheet is changed.

The purpose is to detect any cell in the H column that has a formula, set that as the objective in Solver, set the objective value to the value of 70, by changing the entire C column. The =SUMIF formula that is used dictates what gets added.

 

[JaCharger]

Bump because I've changed the code slightly...

Private Sub SolverSolver()

Dim ws1 As Worksheet
Dim lRow As Long
Dim cell As Range

Set ws1 = Worksheets("Sheet1")
ws1.Activate
lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    'clears h2 to h4 because salarytotals puts the total for the first employee number there 3 times...idk why lol
    ws1.Range("H2:H4").ClearContents

    For Each cell In ws1.Range(Range("H2"), Range("H" & lRow))
        If cell.HasFormula = True Then
            SolverOK setCell:=cell, _
                MaxMinVal:=3, _
                ValueOf:=70, _
                Engine:=1, _
                byChange:=Range("C2:C" & Range("C2").End(xlDown).Row)
            SolverSolve UserFinish:=False
            End If
    Next cell
    
End Sub

I'm still running into the issue where the cell is turned into a reference of itself. Of course, replacing the setCell option with a reference to the cell in SolverOK makes the script run fine, but that defeats the purpose of this.

 

[JaCharger]

The answer to this would be changing

SolverOK setCell:=cell

to

SolverOK setCell:=cell.Address

.