MACRO TO EDIT CELL FORMULA !!

[jay dean]

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!

 

[Tom Ogilvy]

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).

 

[jay dean]

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!

 

[Tom Ogilvy]

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)