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

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

Cells(i,j) = 85

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

--
Regards,
Tom Ogilvy



"trilogylynch" <[email protected]> wrote in message
news:[email protected]...
> 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

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,rw2,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" <[email protected]> wrote in message
news:[email protected]...
> Cells(i,j) = 85
>
> then i and j could be calculated based on some criteria.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "trilogylynch" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>