How do I write macros with relative reference cells
 

I have written a macro , basically a black sholels model for random
simmulation, that works only as long as I do not change the spread sheet
after the macro is recorded.

If I add or delete a row or column, then the macro blows up. I believe the
problem is that the cell references in the macro are specific to each cell
when recorded.

Is there a way around this problem?


John Lynch

How do I write macros with relative reference cells
 

Hi John

if you are working with formulas you have a couple ways of doing it
r1c1
and r[1]c[1]
or a mix
r1c[1]

r1c1 will refer to $A$1
r[1]c[1] will be used as an offset, 1 row, 1 column offset

eg
activecell.formular1c1="=r1c1"
will give you the formula = "=$A$1"
activecell.formular1c1="=r[1]c[1]"
will give you the formula (if activecell is b1)
"=c2"


hth

J




"trilogylynch" wrote:

I have written a macro , basically a black sholels model for random
simmulation, that works only as long as I do not change the spread sheet
after the macro is recorded.

If I add or delete a row or column, then the macro blows up. I believe the
problem is that the cell references in the macro are specific to each cell
when recorded.

Is there a way around this problem?


John Lynch

How do I write macros with relative reference cells
 

Cells(i,j) = 85

then i and j could be calculated based on some criteria.

--
Regards,
Tom Ogilvy



"trilogylynch" wrote in message
...
I have written a macro , basically a black sholels model for random
simmulation, that works only as long as I do not change the spread sheet
after the macro is recorded.

If I add or delete a row or column, then the macro blows up. I believe
the
problem is that the cell references in the macro are specific to each cell
when recorded.

Is there a way around this problem?


John Lynch

How do I write macros with relative reference cells
 

And sometimes you may want to maintain the exact same relative position
so use the offset in your formula

FormulaR1C1="=Sum(Offset(rc,rw1,col1):Offset(RC,rw 2,col2))"

where rw1 & rw2 represent the row offsets
col1 & col2 represent the column offsets
these numbers will never change when rows or columns are added or deleted.

--
steveB

Remove "AYN" from email to respond
"Tom Ogilvy" wrote in message
...
Cells(i,j) = 85

then i and j could be calculated based on some criteria.

--
Regards,
Tom Ogilvy



"trilogylynch" wrote in message
...
I have written a macro , basically a black sholels model for random
simmulation, that works only as long as I do not change the spread sheet
after the macro is recorded.

If I add or delete a row or column, then the macro blows up. I believe
the
problem is that the cell references in the macro are specific to each
cell
when recorded.

Is there a way around this problem?


John Lynch