Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cells from Relative to Absolute Reference | Excel Discussion (Misc queries) | |||
Relative Reference Help Macros | Excel Worksheet Functions | |||
How do I get relative/absolute reference button (macros) | Excel Discussion (Misc queries) | |||
Macros - Relative cells in Excel | Excel Programming | |||
relative reference button missing for recording macros | Excel Programming |