Need to replace cell formula with cell formula 
Author Message
 Need to replace cell formula with cell formula

As always - thanks in advance for any help...

In some of my more complex workbooks (Xcel 97 SR2, WinNT)
Some or all of my formulas on the summary page do not calculate
or do not calculate properly.  Even if I enter the cell and hit enter to
force a recalc of the cell.

So now I have a sub that selects special cells with formulas and loops
through the cells

Sub CellFormulas()
'
' Macro1 Macro
' Macro recorded 3/10/03 by Sbell
'
Dim cel As Range
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select

    For Each cel In Selection
        cel.Formula = cel.Formula
    Next

[A1].Select

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

This takes a upwards of a minute but works like a champ.

My questions:
    1.  Any idea why my formulas wouldn't calculate properly?
            (my workbook DOES have a lot of charts and pivot tables)
    2.  Could there be a way to speed this code up?

Again thanks...

--
steve



Sun, 25 Sep 2005 22:33:17 GMT  
 Need to replace cell formula with cell formula
Hi, :)

Qtn#1: I have no idea why, but I have met same situation before when I
imported data.
         The formulas seemed as strings.

Qtn#2: How about this? (please use the Application.Calculation parts as it
is)

ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Formula

Colo

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Colo - VBA guy! of 'The Road of The Cell Masters'
http://www.interq.or.jp/sun/puremis/colo/CellMastersLink.htm

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_


Quote:
> As always - thanks in advance for any help...

> In some of my more complex workbooks (Xcel 97 SR2, WinNT)
> Some or all of my formulas on the summary page do not calculate
> or do not calculate properly.  Even if I enter the cell and hit enter to
> force a recalc of the cell.

> So now I have a sub that selects special cells with formulas and loops
> through the cells

> Sub CellFormulas()
> '
> ' Macro1 Macro
> ' Macro recorded 3/10/03 by Sbell
> '
> Dim cel As Range
> '
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>     Selection.SpecialCells(xlCellTypeFormulas, 23).Select

>     For Each cel In Selection
>         cel.Formula = cel.Formula
>     Next

> [A1].Select

> Application.Calculation = xlCalculationAutomatic
> Application.ScreenUpdating = True
> End Sub

> This takes a upwards of a minute but works like a champ.

> My questions:
>     1.  Any idea why my formulas wouldn't calculate properly?
>             (my workbook DOES have a lot of charts and pivot tables)
>     2.  Could there be a way to speed this code up?

> Again thanks...

> --
> steve



Mon, 26 Sep 2005 01:45:54 GMT  
 Need to replace cell formula with cell formula
Colo,

Thanks for your suggestion.  Much appreciated.

Works great - EXCEPT on my problem sheet it errors on trying to change a
pivot table.  [Without the pivot your approach would be the way to go]

Did play with my sub and managed to speed it up a bit by turning off/on
ScreenUpdating, Calculation, & Display Alerts.  (now I'll have to see if
it really works in fixing my formulas)

For others interested the project has been cleaned with Code Cleaner to
make sure other problems are at a minimum.

again thanks...

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 26 Sep 2005 16:23:44 GMT  
 Need to replace cell formula with cell formula
Another option might be to do an edit|replace, = with = (equal sign with equal
sign).  Excel should recalc the formulas.  (Not sure why it's not recalculating
now--do you have any UDFs in your workbook.)

Quote:

> Colo,

> Thanks for your suggestion.  Much appreciated.

> Works great - EXCEPT on my problem sheet it errors on trying to change a
> pivot table.  [Without the pivot your approach would be the way to go]

> Did play with my sub and managed to speed it up a bit by turning off/on
> ScreenUpdating, Calculation, & Display Alerts.  (now I'll have to see if
> it really works in fixing my formulas)

> For others interested the project has been cleaned with Code Cleaner to
> make sure other problems are at a minimum.

> again thanks...

> steve

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--

Dave Peterson



Tue, 27 Sep 2005 02:37:52 GMT  
 Need to replace cell formula with cell formula
Dave,

Nice thought, I'll give it a try the next time the problem arises.
(sometimes the simple things are best)

Some history:
  1.  My Personal.xls is > 500 kb
  2.  My *.xlb is ~ 500 kb
  3.  My main workbook is > 5 Mb
  4.  The problem workbook(s) is ~ 5 MB with > 300 charts
      and a number of pivot tables
  5.  All workbooks have been cleaned with CodeCleaner
  6.  My formula replace code does "fix" the problem (but
      this is a bandaide...
      But why would this work when entering the cell and
      hitting Enter doesn't?  Shouldn't this be the same?
  7.  Smaller workbooks with the same format and < 100
      charts don't seem to have the problem

And there are NO UDF's.  (they scare me because of their
weird calculation traits)

Thanks for any insight!

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 27 Sep 2005 15:41:33 GMT  
 Need to replace cell formula with cell formula
Netscape crashed.  ughhhh.

First, take 30 minutes to rebuild your toolbars.  Then back it up so that you
can just overwrite the *.xlb when it starts growing.

I'm sure you've seen messages about excel crashing.  Lots of responses point at
a bloated .xlb file.  (I cleaned mine up and went from about 450k to 12k.
(amazing!).  It seems like a nice preventative measure.  (I had already finished
most of this post when NS went south.  It was less than 5 minutes lost.  Imagine
xl crashing while you're in your favorite project after 15 minutes of intensive
changes.)

(Keeping the temp folder clean is another preventative measure that's easy to
do, too.)

But back to the subject.  I've never seen xl do this in real life (formulas not
recalculating), but there have been a few posts that mimic your problem.

(maybe the number is small because it's limited to xl97??????)

You may want to visit Charles Williams' site to find out more secrets about
calculation:
http://www.decisionmodels.com/

Quote:

> Dave,

> Nice thought, I'll give it a try the next time the problem arises.
> (sometimes the simple things are best)

> Some history:
>   1.  My Personal.xls is > 500 kb
>   2.  My *.xlb is ~ 500 kb
>   3.  My main workbook is > 5 Mb
>   4.  The problem workbook(s) is ~ 5 MB with > 300 charts
>       and a number of pivot tables
>   5.  All workbooks have been cleaned with CodeCleaner
>   6.  My formula replace code does "fix" the problem (but
>       this is a bandaide...
>       But why would this work when entering the cell and
>       hitting Enter doesn't?  Shouldn't this be the same?
>   7.  Smaller workbooks with the same format and < 100
>       charts don't seem to have the problem

> And there are NO UDF's.  (they scare me because of their
> weird calculation traits)

> Thanks for any insight!

> steve

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--

Dave Peterson



Wed, 28 Sep 2005 01:46:34 GMT  
 Need to replace cell formula with cell formula
Dave,

Guess I'm gonna need to follow your advice and bite the bullet.  Ugh!
Do I just rename the original *.xlb, delete all my custom toolbars and
just start rebuilding?

I do save verrrry frequently to avoid losing large blocks of changes...

Interesting about Mr Williams!  Sometime back I got his Fast Excel
software and am still puzzled by it.  It goes through your workbook and
comes up with all kinds of tables and other infor - but all this info is
greek to me.  It kind of shows where the calculation bottlenecks are,
but I am not sure of how to use it.

One thing it can do is rearrange your worksheets to make them calculate
faster.  Nice idea, but it totally messed up the way I had my workbook
organized.  (good thing I worked on a copy and not the original).  

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 30 Sep 2005 16:02:04 GMT  
 Need to replace cell formula with cell formula
Depending on the complexity of your toolbar, you can make notes (ugh back at
you!) or just show your toolbars and do alt-print screen.

Paste that picture into word(?) and print it.

You can rename it (*.old) just in case you don't remember one of the icons.  But
after I printed mine, I just dumped the old toolbar file.  

In fact, when I recreated the new one, some infrequent buttons didn't get added
back, but others that I never noticed did get added.  (Doesn't hurt to look
inside there every once in awhile!)

But after you're done, back it up.  (and when you make changes???)  You won't
have to do this again.

Quote:

> Dave,

> Guess I'm gonna need to follow your advice and bite the bullet.  Ugh!
> Do I just rename the original *.xlb, delete all my custom toolbars and
> just start rebuilding?

> I do save verrrry frequently to avoid losing large blocks of changes...

> Interesting about Mr Williams!  Sometime back I got his Fast Excel
> software and am still puzzled by it.  It goes through your workbook and
> comes up with all kinds of tables and other infor - but all this info is
> greek to me.  It kind of shows where the calculation bottlenecks are,
> but I am not sure of how to use it.

> One thing it can do is rearrange your worksheets to make them calculate
> faster.  Nice idea, but it totally messed up the way I had my workbook
> organized.  (good thing I worked on a copy and not the original).

> steve

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--

Dave Peterson



Sat, 01 Oct 2005 02:47:02 GMT  
 Need to replace cell formula with cell formula
Dave,

Thanks for the encouragement!  I'll have to just Do It!

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 01 Oct 2005 15:43:34 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. need cell formulas to display results not the formula

2. Need to know if a cell has text or a formula referencing other cells

3. Summing using formulas already in cells, not cell references, or values in cells

4. I need help with how to use a formula to replace a formula

5. Replace Formulae With Cell Values.

6. Problems replacing cell reference with name in formulas

7. Excel 2000, replace part of formula with contents of another cell

8. Formulae to replace cell values

9. Using cell values or formulas to create new formulas in macro

10. Putting the results of a formula, rather than the formula in a cell with a sub


 
Powered by phpBB® Forum Software © phpBB Group