Macro Auto Change Cell References
The problem is that your cell id's are just text strings. They do not
adjust. Set up a Defined Name in the worksheet, say benign to cover cell
D54, then instead of:
SolverOk SetCell:="D54"
you can use:
Dim s As String
s = Range("benign").Address(RowAbsolute:=False, ColumnAbsolute:=False)
SolverOk SetCell:=s
The Named Range "benign" will adjust as rows/columns are inserted/deleted.
--
Gary''s Student - gsnu200774
"Chrissie" wrote:
Hello,
I have a macro which uses the solve function to solve for various cells in
my worksheet. The problem is I have noticed whenever I am working on my sheet
and insert/delete a column and/or row my macro's cell reference values do not
change automatically. (Like a formula within a worksheet change automatically
when you insert rows above it)
Here is an example of my macro code:
SolverOk SetCell:="D54", MaxMinVal:=1, ValueOf:="0", ByChange:="D57"
SolverAdd CellRef:="D54", Relation:=2, FormulaText:="D55"
Any suggestions? Thanks.
|