Hi Shankfoot,
Assuming that you will have a similar setup in the other two tables as you
have in column D so that the cells that end up with circular references a
D20, H20 & L20 then it should be fairly simple .
You will notice have that the formula that is in H20 only referred to Column
H so when it was copied to D20 it then only referred to Column D. This
means that we can copy the formula to N20 and use that one formula to copy
to cells D20, H20 &L20.
With Macros disabled, (otherwise they may change things back after you
change them), I unhid Column H, copied the formula in H20 to N20,(or you can
just type into N20 the forumula =N12-N18 Note that the foumula MUST refer to
the same column that the formula is in and the cell MUST be formatted the
same as you want cells D20, H20 & L20 to be because when the Macro pastes in
the new formula the formatting will automatically be changed to that of cell
N20. Also note that you don't need a formula in P20 or R20.), I then hid
Column N.
Next I changed the CalculateIt() Macro to:
Sub CalculateIt()
With Sheets("Lease Worksheet")
.Range("N20").Copy .Range("D20")
.Range("D20").Copy
.Range("D20").PasteSpecial Paste:=xlValues
.Range("N20").Copy .Range("H20")
.Range("H20").Copy
.Range("H20").PasteSpecial Paste:=xlValues
.Range("N20").Copy .Range("L20")
.Range("L20").Copy
.Range("L20").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
End Sub
I tested it by creating two other tables with the calculations in Columns D,
H & L and all three tables updated as expected. I found by experimentation
that the .Calculate line that was in my original code was not required -
Excel calculates when the formulas are pasted in - although I am sure that
it wouldn't do so when I was originally trying out the code.
--
HTH
Sandy
with @tiscali.co.uk
"sharkfoot" wrote
in message ...
Awesome. I have one more question. I need to have this done 3 times in
this sheet. In other words, what do I need to change to have the D, H,
and L columns (we already have D working) all figure this same formula?
I have the formulas for D20 copied into N20. H20 copied into P20 and L20
copied into R20. I'm don't know if I need to add to the current module
or just add 2 more modules for each of the new target cells.
Can you tell me wat todo to get these other 2 leases working? Thanks!
Sandy Mann Wrote:
Perhaps I should have explained why the worksheet went wrong for you.
It looks like you either copied only the SUM(D12-D18) without the =
sign or
simply typed into cell H20 exactly what is in Cell D20.
If you simply click into a cell and click copy either by the toolbar
button
or the right-click menu and then click into another cell and paste
Excel
will automatically adjust the reference to the new location. For
example in
cell H2 enter the formula =D2 Now click back into the cell and copy it
and
paste into cell E2. The formula that you have just pasted into Cell E2
will
now be =A2. Excel changed the formula which was referencing a cell
four
columns to the left of the original to be still referencing a cell
four
columns to the left but not from the NEW location.
Next copy cell H2 again and now paste it into cell D2 - you will get a
#REF!
error! Why? Because it is still referencing a cell four columns to the
left
but now there is no cell four columns to the left of D2 so Excel alerts
you
to this by giving you a #REF! error.
An exception to this is when you make the reference ABSOLUTE as in
=$D$2.
This will always refer to cell D2 even if you paste it into cell A10.
Look
up *Move or copy a formula* in Help.
By copying the formula in D20 to H20 in the first place it changes it
from
=D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create
a
circular reference. However, when it gets copied back into D20 by the
Macro
of course it does create the circular reference again but the Macro
goes on
to paste the contents of the cell as a constant thus removing the
circular
reference error once more.
--
HTH
Sandy
with @tiscali.co.uk
--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile:
http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=525887