software

I have a workbook (attached to the message so take a peek) with a
circular reference that, apparently, has got to be there. I added a
private sub to make sure the sheet keeps the ITERATION checkbox
selected:Code:
--------------------
Private Sub Workbook_Open()
Application.Iteration = True
End Sub
--------------------One problem still is that the sub apparently goes into effect AFTER the
workbook checks for circular references. What happens is that the person
opening the workbook gets the quot;excel cannot do thisquot; message that is
rather unsightly and potentially confusing to the user.

How do I:

A. Get rid of (supress?) the warning box that appears or
B. Have the sub get called upon BEFORE the workbook checks for circular
references?
C. Figure out a way to avoid the circular reference altogether.

Anything would be good at this point. Please help! -------------------------------------------------------------------
|Filename: lease.zip |
|Download: www.excelforum.com/attachment.php?postid=4516 |
-------------------------------------------------------------------

--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: www.excelforum.com/member.php...oamp;userid=32164
View this thread: www.excelforum.com/showthread...hreadid=525887
No one brave enough to take a stab at this?--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: www.excelforum.com/member.php...oamp;userid=32164
View this thread: www.excelforum.com/showthread...hreadid=525887Shankfoot,

The only cell that seems to have a circular reference is D20 so I would
suggest changing the formula to a constant as follows:

First of all copy the formula in D20 and paste it into cell H20, (or any
other cell but you will have to change the references in the Macros to
suite), then hide column H

In the This Workbook Module change your Macro to:

Private Sub Workbook_Open()
Application.Iteration = True
Application.EnableEvents = False
CalculateIt
Application.EnableEvents = True
End Sub

and add:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Iteration = False
End Sub

Next in a normal Module add:

Sub CalculateIt()
With Sheets(quot;Lease Worksheetquot;)
.Range(quot;H20quot;).Copy .Range(quot;D20quot;)
.Calculate
.Range(quot;D20quot;).Copy
.Range(quot;D20quot;).PasteSpecial _
Paste:=xlValues
Application.CutCopyMode = False
End With
End Sub

Right-click on the quot;Data Entryquot; tab and select quot;View Codequot; and enter in the
sheet Module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
CalculateIt
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Next do the same to the quot;Lease Worksheetquot; and insert the same code as in
quot;Data Entryquot;

The sheet should then calculate without any *Calculate* or *Circular
Reference* alerts

--
HTH

Sandy
with @tiscali.co.uk
quot;sharkfootquot; gt; wrote
in message ...
gt;
gt; I have a workbook (attached to the message so take a peek) with a
gt; circular reference that, apparently, has got to be there. I added a
gt; private sub to make sure the sheet keeps the ITERATION checkbox
gt; selected:
gt;
gt;
gt; Code:
gt; --------------------
gt; Private Sub Workbook_Open()
gt; Application.Iteration = True
gt; End Sub
gt; --------------------
gt;
gt;
gt; One problem still is that the sub apparently goes into effect AFTER the
gt; workbook checks for circular references. What happens is that the person
gt; opening the workbook gets the quot;excel cannot do thisquot; message that is
gt; rather unsightly and potentially confusing to the user.
gt;
gt; How do I:
gt;
gt; A. Get rid of (supress?) the warning box that appears or
gt; B. Have the sub get called upon BEFORE the workbook checks for circular
gt; references?
gt; C. Figure out a way to avoid the circular reference altogether.
gt;
gt; Anything would be good at this point. Please help!
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: lease.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4516 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; sharkfoot
gt; ------------------------------------------------------------------------
gt; sharkfoot's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32164
gt; View this thread: www.excelforum.com/showthread...hreadid=525887
gt;

Attached is the file after I made the changes you suggested. As you can
see, something is very, very wrong but I'm not sure what. Can you tell
me what went wrong?

Sandy Mann Wrote:
gt; Shankfoot,
gt;
gt; The only cell that seems to have a circular reference is D20 so I
gt; would
gt; suggest changing the formula to a constant as follows:
gt;
gt; First of all copy the formula in D20 and paste it into cell H20, (or
gt; any
gt; other cell but you will have to change the references in the Macros to
gt; suite), then hide column H
gt;
gt; In the This Workbook Module change your Macro to:
gt;
gt; Private Sub Workbook_Open()
gt; Application.Iteration = True
gt; Application.EnableEvents = False
gt; CalculateIt
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; and add:
gt;
gt; Private Sub Workbook_BeforeClose(Cancel As Boolean)
gt; Application.Iteration = False
gt; End Sub
gt;
gt; Next in a normal Module add:
gt;
gt; Sub CalculateIt()
gt; With Sheets(quot;Lease Worksheetquot;)
gt; .Range(quot;H20quot;).Copy .Range(quot;D20quot;)
gt; .Calculate
gt; .Range(quot;D20quot;).Copy
gt; .Range(quot;D20quot;).PasteSpecial _
gt; Paste:=xlValues
gt; Application.CutCopyMode = False
gt; End With
gt; End Sub
gt;
gt; Right-click on the quot;Data Entryquot; tab and select quot;View Codequot; and enter in
gt; the
gt; sheet Module:
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; Application.ScreenUpdating = False
gt; Application.EnableEvents = False
gt; CalculateIt
gt; Application.EnableEvents = True
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt; Next do the same to the quot;Lease Worksheetquot; and insert the same code as
gt; in
gt; quot;Data Entryquot;
gt;
gt; The sheet should then calculate without any *Calculate* or *Circular
gt; Reference* alerts
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt; -------------------------------------------------------------------
|Filename: lease1.zip |
|Download: www.excelforum.com/attachment.php?postid=4523 |
-------------------------------------------------------------------

--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: www.excelforum.com/member.php...oamp;userid=32164
View this thread: www.excelforum.com/showthread...hreadid=525887Hi Shankfoot,

What is wrong is that the formula you have in the hidden H20 is reading
=SUM(D12-D18) when it should be referencing H12 amp; H18.

I fixed it by:

Open the file with Macros disabled, unhide Column H and change the formula
in H20 to =H12-H18 (The SUM part is not required.)

Hide Column H again and put any number into D20. This will remove all the
#REF! errors.

Save the spreadsheet under another name and close it. Now open the new
spreadsheet again and enable Macros. After that it worked for me again.

(Incidentally the SUM part is not required in D12 either or you can change
it to =SUM(D611), similarly D26 amp; D32 don't require a SUM either).

If you have any more trouble the do post back again.

--
HTH

Sandy
with @tiscali.co.ukquot;sharkfootquot; gt; wrote
in message ...
gt;
gt; Attached is the file after I made the changes you suggested. As you can
gt; see, something is very, very wrong but I'm not sure what. Can you tell
gt; me what went wrong?
gt;

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
quot;Sandy Mannquot; gt; wrote in message
...
gt; Hi Shankfoot,
gt;
gt; What is wrong is that the formula you have in the hidden H20 is reading
gt; =SUM(D12-D18) when it should be referencing H12 amp; H18.
gt;
gt; I fixed it by:
gt;
gt; Open the file with Macros disabled, unhide Column H and change the formula
gt; in H20 to =H12-H18 (The SUM part is not required.)
gt;
gt; Hide Column H again and put any number into D20. This will remove all the
gt; #REF! errors.
gt;
gt; Save the spreadsheet under another name and close it. Now open the new
gt; spreadsheet again and enable Macros. After that it worked for me again.
gt;
gt; (Incidentally the SUM part is not required in D12 either or you can change
gt; it to =SUM(D611), similarly D26 amp; D32 don't require a SUM either).
gt;
gt; If you have any more trouble the do post back again.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;sharkfootquot; gt; wrote
gt; in message ...
gt;gt;
gt;gt; Attached is the file after I made the changes you suggested. As you can
gt;gt; see, something is very, very wrong but I'm not sure what. Can you tell
gt;gt; me what went wrong?
gt;gt;
gt;
gt;
gt;

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:
gt; Perhaps I should have explained why the worksheet went wrong for you.
gt;
gt; It looks like you either copied only the SUM(D12-D18) without the =
gt; sign or
gt; simply typed into cell H20 exactly what is in Cell D20.
gt;
gt; If you simply click into a cell and click copy either by the toolbar
gt; button
gt; or the right-click menu and then click into another cell and paste
gt; Excel
gt; will automatically adjust the reference to the new location. For
gt; example in
gt; cell H2 enter the formula =D2 Now click back into the cell and copy it
gt; and
gt; paste into cell E2. The formula that you have just pasted into Cell E2
gt; will
gt; now be =A2. Excel changed the formula which was referencing a cell
gt; four
gt; columns to the left of the original to be still referencing a cell
gt; four
gt; columns to the left but not from the NEW location.
gt;
gt; Next copy cell H2 again and now paste it into cell D2 - you will get a
gt; #REF!
gt; error! Why? Because it is still referencing a cell four columns to the
gt; left
gt; but now there is no cell four columns to the left of D2 so Excel alerts
gt; you
gt; to this by giving you a #REF! error.
gt;
gt; An exception to this is when you make the reference ABSOLUTE as in
gt; =$D$2.
gt; This will always refer to cell D2 even if you paste it into cell A10.
gt; Look
gt; up *Move or copy a formula* in Help.
gt;
gt; By copying the formula in D20 to H20 in the first place it changes it
gt; from
gt; =D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create
gt; a
gt; circular reference. However, when it gets copied back into D20 by the
gt; Macro
gt; of course it does create the circular reference again but the Macro
gt; goes on
gt; to paste the contents of the cell as a constant thus removing the
gt; circular
gt; reference error once more.
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: www.excelforum.com/member.php...oamp;userid=32164
View this thread: www.excelforum.com/showthread...hreadid=525887Hi 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 amp; 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 amp;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 amp; 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(quot;Lease Worksheetquot;)
.Range(quot;N20quot;).Copy .Range(quot;D20quot;)
.Range(quot;D20quot;).Copy
.Range(quot;D20quot;).PasteSpecial Paste:=xlValues

.Range(quot;N20quot;).Copy .Range(quot;H20quot;)
.Range(quot;H20quot;).Copy
.Range(quot;H20quot;).PasteSpecial Paste:=xlValues

.Range(quot;N20quot;).Copy .Range(quot;L20quot;)
.Range(quot;L20quot;).Copy
.Range(quot;L20quot;).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 amp; 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

quot;sharkfootquot; gt; wrote
in message ...
gt;
gt; Awesome. I have one more question. I need to have this done 3 times in
gt; this sheet. In other words, what do I need to change to have the D, H,
gt; and L columns (we already have D working) all figure this same formula?
gt; I have the formulas for D20 copied into N20. H20 copied into P20 and L20
gt; copied into R20. I'm don't know if I need to add to the current module
gt; or just add 2 more modules for each of the new target cells.
gt;
gt; Can you tell me wat todo to get these other 2 leases working? Thanks!
gt;
gt; Sandy Mann Wrote:
gt;gt; Perhaps I should have explained why the worksheet went wrong for you.
gt;gt;
gt;gt; It looks like you either copied only the SUM(D12-D18) without the =
gt;gt; sign or
gt;gt; simply typed into cell H20 exactly what is in Cell D20.
gt;gt;
gt;gt; If you simply click into a cell and click copy either by the toolbar
gt;gt; button
gt;gt; or the right-click menu and then click into another cell and paste
gt;gt; Excel
gt;gt; will automatically adjust the reference to the new location. For
gt;gt; example in
gt;gt; cell H2 enter the formula =D2 Now click back into the cell and copy it
gt;gt; and
gt;gt; paste into cell E2. The formula that you have just pasted into Cell E2
gt;gt; will
gt;gt; now be =A2. Excel changed the formula which was referencing a cell
gt;gt; four
gt;gt; columns to the left of the original to be still referencing a cell
gt;gt; four
gt;gt; columns to the left but not from the NEW location.
gt;gt;
gt;gt; Next copy cell H2 again and now paste it into cell D2 - you will get a
gt;gt; #REF!
gt;gt; error! Why? Because it is still referencing a cell four columns to the
gt;gt; left
gt;gt; but now there is no cell four columns to the left of D2 so Excel alerts
gt;gt; you
gt;gt; to this by giving you a #REF! error.
gt;gt;
gt;gt; An exception to this is when you make the reference ABSOLUTE as in
gt;gt; =$D$2.
gt;gt; This will always refer to cell D2 even if you paste it into cell A10.
gt;gt; Look
gt;gt; up *Move or copy a formula* in Help.
gt;gt;
gt;gt; By copying the formula in D20 to H20 in the first place it changes it
gt;gt; from
gt;gt; =D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create
gt;gt; a
gt;gt; circular reference. However, when it gets copied back into D20 by the
gt;gt; Macro
gt;gt; of course it does create the circular reference again but the Macro
gt;gt; goes on
gt;gt; to paste the contents of the cell as a constant thus removing the
gt;gt; circular
gt;gt; reference error once more.
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;
gt;
gt; --
gt; sharkfoot
gt; ------------------------------------------------------------------------
gt; sharkfoot's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32164
gt; View this thread: www.excelforum.com/showthread...hreadid=525887
gt;
Sandy Mann Wrote:
gt; Hi Shankfoot,
gt;
gt; Assuming that you will have a similar setup in the other two tables as
gt; you
gt; have in column D so that the cells that end up with circular references
gt; a
gt; D20, H20 amp; L20 then it should be fairly simple .

Thank you so much. This works perfectly. And the way you explain things
helps me learn much better than someone just fixing the problem for me.
Thanks again!--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: www.excelforum.com/member.php...oamp;userid=32164
View this thread: www.excelforum.com/showthread...hreadid=525887You're welcome Sharkfoot,

quot;sharkfootquot; gt; wrote
in message ...

gt; Thank you so much. This works perfectly. And the way you explain things
gt; helps me learn much better than someone just fixing the problem for me.

quot;Sandy Mannquot; gt; wrote in message
...

gt; H amp; L and all three tables updated as expected. I found by
gt; experimentation
gt; that the .Calculate line that was in my original code was not required -
gt; Excel calculates when the formulas are pasted in

Seems like we both learned for the experience.

--
Regards

Sandy
with @tiscali.co.uk