Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO EDIT CELL FORMULA !!
Please help. This thing is driving me nuts !! I need a macro that will paste an altered copy of a cell formula into another cell. Example - cell B10 contains ='SheetX'!K22 When I copy and paste its formula in any cell, say D6, cell D6 should contain='SheetX'!K23 Another example - say cell F4 contains='SheetX'!P10 When I copy and paste that formula in any cell, like J5, cell J5 should contain='SheetX'!P11 I have lots of cell formulas to change the row portion of their formulas just by 1 and doing this manually is painful and tedious. Please help with a code. Thanks in advance!! Jay Dean *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO EDIT CELL FORMULA !!
there is no built in support for anything like this. You would need to
parse out the row number, increment it by 1, then append it to the remainder of the formula. If the formula didn't pertain to another worksheet, you might be able to use directprecedents, but you example appears to point to another worksheet. (you might be able to cobble together something using the xl4 macro equivalent to trace arrow). -- Regards, Tom Ogilvy "jay dean" wrote in message ... Please help. This thing is driving me nuts !! I need a macro that will paste an altered copy of a cell formula into another cell. Example - cell B10 contains ='SheetX'!K22 When I copy and paste its formula in any cell, say D6, cell D6 should contain='SheetX'!K23 Another example - say cell F4 contains='SheetX'!P10 When I copy and paste that formula in any cell, like J5, cell J5 should contain='SheetX'!P11 I have lots of cell formulas to change the row portion of their formulas just by 1 and doing this manually is painful and tedious. Please help with a code. Thanks in advance!! Jay Dean you *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO EDIT CELL FORMULA !!
Tom - - Can you give an example of how to parse out the row number? - Also can you elaborate a bit on how to use directprecedents? I think if you make these two approaches a little bit clearer to me I may be able to use them. Thanks in advance !! Jay Dean *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO EDIT CELL FORMULA !!
DirectPrecedents doesn't work for precedents on other sheets. If you have a
formula like =Sum(A1:B10) in the activecell and ran Sub ShowPrecedents() MsgBox ActiveCell.DirectPrecedents.Address End Sub It would display $A$1:$B$10 If you are using Excel 2000 or later, using your formulas as examples Dim sForm as String, varr as Variant sForm = Range("B9").formula varr = Split(sForm,"!") range("Q20").Formula = varr(0) & "!" & _ range(varr(1)).offset(1,0).Address(0,0) -- Regards, Tom Ogilvy "jay dean" wrote in message ... Tom - - Can you give an example of how to parse out the row number? - Also can you elaborate a bit on how to use directprecedents? I think if you make these two approaches a little bit clearer to me I may be able to use them. Thanks in advance !! Jay Dean *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to edit formula in cell | Excel Discussion (Misc queries) | |||
Macro to edit formula | Excel Discussion (Misc queries) | |||
Macro to edit cell | Excel Programming | |||
macro to edit formula | Excel Programming | |||
macro to edit formula | Excel Programming |