×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SolverOk ByChange overwrite in Excel 2007 VBA

SolverOk ByChange overwrite in Excel 2007 VBA

SolverOk ByChange overwrite in Excel 2007 VBA

(OP)
The objective was to reconfigure Solver to use different sets of optimization variables (20+ of them) easily using different sets of cell background colours.

While trying this I encountered an interesting 'feature' (aka bug) in VBA Solver. I created a Range by searching for a given cell background format:

CODE

    first = True
    Set globalRange = Range("A2:AH100")
    colourSpec = Range("A1").Interior.ColorIndex
    For Each localCell In globalRange
        If localCell.Interior.ColorIndex = colourSpec Then
            If first Then
                first = False
                Set solverVariableRange = Range(localCell.Address)
            Else
                Set solverVariableRange = Union(solverVariableRange, Range(localCell.Address))
            End If
        End If
    Next localCell
Then, I set the SolverOk to use that Range:

CODE

SolverOk SetCell:="$U$30", MaxMinVal:=2, _ ByChange:=solverVariableRange
SolverOk pasted the range *as text* into each cell of the given range. Seriously. My spreadsheet already has a number of named ranges, so maybe Excel ran out of bits.

The solution was given by Joerd at http://www.eng-tips.com/viewthread.cfm?qid=199259

CODE

ActiveSheet.Names.Add Name:="solver_adj", RefersTo:=solverVariableRange, Visible:=False
Excel has graciously agreed to no longer overwrite my hard won starting values. I thought this info could be of use to others and I'm infinitely grateful to SteveGregory for the question and Joerd for the solution.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources