Macro Auto Change Cell References

[Chrissie]

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.

 

[Gary''s Student]

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.