Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Calculating problem
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Calculating problem
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 -- Gary''s Student - gsnu200790 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Calculating problem
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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200790 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Calculating problem
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 wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Calculating problem
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. "Dave Peterson" wrote: 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 wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem calculating with a SUMIF | Excel Worksheet Functions | |||
microsoft excel 2000 calculating problem | Excel Discussion (Misc queries) | |||
Problem Plotting/Calculating with #NA | Excel Discussion (Misc queries) | |||
Time calculating problem | Excel Discussion (Misc queries) | |||
Problem with calculating time | Excel Discussion (Misc queries) |