Help solve a Circular Reference
 

I have a complex workbook containing 150 worksheets. I am trying to record a
macro to reset all of the pages at once. I can clear all information, copy
information needed to another cell but when I try to update the dates (from
the last date to a new date) I receive a circular reference error. When I
change the calculation to 2X max I remove the circular reference, but it then
will not save. With 1x max only half of the dates are updated, with 3x max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie: 02/15/2005, cell F47
then is the end date 3 months later. If I write in the macro to update A4 to
F47+1, I receive the circular reference. In all of the other worksheets, cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest of my "update" macro
works.
Any thoughts and/or remedies?

Hi!

Here's what I see ...

If the end date in F47 is calculated based on the inputed
date in A4, then you try to update A4 to be F47+1, there's
your circular reference.

Maybe something like this will help: before you try to
update cell A4 with F47+1 do a paste special values on F47.

Biff

-----Original Message-----
I have a complex workbook containing 150 worksheets. I am
trying to record a
macro to reset all of the pages at once. I can clear all
information, copy
information needed to another cell but when I try to
update the dates (from
the last date to a new date) I receive a circular
reference error. When I
change the calculation to 2X max I remove the circular
reference, but it then
will not save. With 1x max only half of the dates are
updated, with 3x max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie:
02/15/2005, cell F47
then is the end date 3 months later. If I write in the
macro to update A4 to
F47+1, I receive the circular reference. In all of the
other worksheets, cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest
of my "update" macro
works.
Any thoughts and/or remedies?
.

For each sh in thisworkbook.Worksheets
sh.Range("A4").Value = sh.Range("F47").Value + 1
Next

--
Regards,
Tom Ogilvy


"brupub" wrote in message
...
I have a complex workbook containing 150 worksheets. I am trying to record
a
macro to reset all of the pages at once. I can clear all information, copy
information needed to another cell but when I try to update the dates
(from
the last date to a new date) I receive a circular reference error. When I
change the calculation to 2X max I remove the circular reference, but it
then
will not save. With 1x max only half of the dates are updated, with 3x
max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie: 02/15/2005, cell
F47
then is the end date 3 months later. If I write in the macro to update A4
to
F47+1, I receive the circular reference. In all of the other worksheets,
cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest of my "update"
macro
works.
Any thoughts and/or remedies?

Biff: What special value? Remember I am trying to keep this simple by writing
a macro to update or restet the entire workbook. My macro already
accomplishes everything except the date update. I can update the entire
workbook by simply reentering the new date, but I am trying to make the macro
all-encompassing. When I change to calculation to iteration and a 1x max, the
result works, but only for one-half the dates. any thoughts with this
approach? Am I understanding the premise of iterations correctly. Thanks

"Biff" wrote:

Hi!

Here's what I see ...

If the end date in F47 is calculated based on the inputed
date in A4, then you try to update A4 to be F47+1, there's
your circular reference.

Maybe something like this will help: before you try to
update cell A4 with F47+1 do a paste special values on F47.

Biff

-----Original Message-----
I have a complex workbook containing 150 worksheets. I am
trying to record a
macro to reset all of the pages at once. I can clear all
information, copy
information needed to another cell but when I try to
update the dates (from
the last date to a new date) I receive a circular
reference error. When I
change the calculation to 2X max I remove the circular
reference, but it then
will not save. With 1x max only half of the dates are
updated, with 3x max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie:
02/15/2005, cell F47
then is the end date 3 months later. If I write in the
macro to update A4 to
F47+1, I receive the circular reference. In all of the
other worksheets, cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest
of my "update" macro
works.
Any thoughts and/or remedies?
.

Tom: See reply to Biff. Thanks

"Tom Ogilvy" wrote:

For each sh in thisworkbook.Worksheets
sh.Range("A4").Value = sh.Range("F47").Value + 1
Next

--
Regards,
Tom Ogilvy


"brupub" wrote in message
...
I have a complex workbook containing 150 worksheets. I am trying to record
a
macro to reset all of the pages at once. I can clear all information, copy
information needed to another cell but when I try to update the dates
(from
the last date to a new date) I receive a circular reference error. When I
change the calculation to 2X max I remove the circular reference, but it
then
will not save. With 1x max only half of the dates are updated, with 3x
max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie: 02/15/2005, cell
F47
then is the end date 3 months later. If I write in the macro to update A4
to
F47+1, I receive the circular reference. In all of the other worksheets,
cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest of my "update"
macro
works.
Any thoughts and/or remedies?

Tom:
thanks for the macro code, I will try it and see if I can get it into my
full macro. Appreciate the help.
brupub

"Tom Ogilvy" wrote:

For each sh in thisworkbook.Worksheets
sh.Range("A4").Value = sh.Range("F47").Value + 1
Next

--
Regards,
Tom Ogilvy


"brupub" wrote in message
...
I have a complex workbook containing 150 worksheets. I am trying to record
a
macro to reset all of the pages at once. I can clear all information, copy
information needed to another cell but when I try to update the dates
(from
the last date to a new date) I receive a circular reference error. When I
change the calculation to 2X max I remove the circular reference, but it
then
will not save. With 1x max only half of the dates are updated, with 3x
max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie: 02/15/2005, cell
F47
then is the end date 3 months later. If I write in the macro to update A4
to
F47+1, I receive the circular reference. In all of the other worksheets,
cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest of my "update"
macro
works.
Any thoughts and/or remedies?

Tom: I'm getting a 400 error with this macro. Planned on copying into my long
macro when it works.
Thanks, Larry

"Tom Ogilvy" wrote:

For each sh in thisworkbook.Worksheets
sh.Range("A4").Value = sh.Range("F47").Value + 1
Next

--
Regards,
Tom Ogilvy


"brupub" wrote in message
...
I have a complex workbook containing 150 worksheets. I am trying to record
a
macro to reset all of the pages at once. I can clear all information, copy
information needed to another cell but when I try to update the dates
(from
the last date to a new date) I receive a circular reference error. When I
change the calculation to 2X max I remove the circular reference, but it
then
will not save. With 1x max only half of the dates are updated, with 3x
max,
the calculations go on twice more. Example:
Cell A4 in worksheet one has a simple inputted date ie: 02/15/2005, cell
F47
then is the end date 3 months later. If I write in the macro to update A4
to
F47+1, I receive the circular reference. In all of the other worksheets,
cell
A4 is hyperlinked back to cell A4, worksheet 1. The rest of my "update"
macro
works.
Any thoughts and/or remedies?