Excel Calculating problem

[JohnS-BelmontNC]

when i programatically copied and pasted a formula on a worksheet, the entire
worksheet now refuses to calculate all formulas - even simple ones, entered
manually, such as = 1+2. 0 is displayed as the result.

Cell formatting is set to standard Number, 0 Decimals and (1,234) for
negative.


Here's the code I used to copy and paste the formula:

with WorkSheetObject
.cells(9,5).formula = "=E41+E73+E105"

.Cells(9, 5).Copy
.Range(.Cells(9, 6), .Cells(9, 42)).PasteSpecial _
Paste:=xlPasteFormulasAndNumberFormats
end with

All was working fine until I ran these two lines. Had same issue earlier on
different sheet using sumif after programatically using PasteSpecial,
developed a workaround but I really need this formula to work on this sheet

Am Using Excel 2002 SP3 in WIn XP Pro. No other Office versions have been
on machine. Calculation is set to Automatic and forcing re-calulation has no
effect (Calc Now "F9" or Calc Sheet).

Any helpful comments are appreciated.

 

[Gary''s Student]

Sub hskf()
With ActiveSheet
.Cells(9, 5).Formula = "=E41+E73+E105"

.Cells(9, 5).Copy
.Range(.Cells(9, 6), .Cells(9, 42)).PasteSpecial _
Paste:=xlPasteFormulasAndNumberFormats
End With

End Sub

 

[JohnS-BelmontNC]

aI don't see any significant difference between what I originally worte and
the response. code was already in a proc (assumed) and WorkSheetObject refers
to a worksheet (and must) other than the activesheet.

When I copy the worksheet (drag and drop) to a new sheet it starts to
calculate.

 

[Dave Peterson]

I've seen some posts that say that calculation won't occur even when calculation
is set to automatic.

One suggestion that seems to work is to select all the cells
edit|Replace
what: = (equal sign)
with: =
replace all

It seems to wake up excel's calculation engine.

I would try that first, then try your code once more.

If that doesn't work, maybe you could include the same technique in your code:

Option Explicit
Sub testme01()

Dim WorkSheetObject As Worksheet

Set WorkSheetObject = ActiveSheet

With WorkSheetObject
With .Range("E9").Resize(1, 38)
.Formula = "=E41+E73+E105"
Application.Calculate
'if that didn't work, "reenter" the formulas
.Replace what:="=", replacement:="=", Lookat:=xlPart, _
searchorder:=xlByColumns, MatchCase:=False
End With
End With
End Sub

 

[JohnS-BelmontNC]

I've since copied the sheet and programmed around PasteSpecial using .Copy
and .Paste Destination:= .Range(...)

After this I created a code line to format the copied range useing
..Range(...).NumberFormat = "##,###,##0"

This seems to have solved the problem as a workaround. Unfortunately, this
section of code will get a good workout because it will be re-run each time
as indivduals add a records to a previous sheet.

I've gone back to the old non-working sheet and it will now re-calculate. It
even works programatically. Lord know why!

Since this workbook will be used by others and many values will be
dynamically updated, I can't take the chance of having it not work at least
not at this stage, so I use the workaround. Perhaps when I move to Office
2007 it wil be different.

I assume this is a bug in Excel 2002 or VBA.

Thanks for your input.