Hey, I may not be able to help with the solution but may provide some
possibly useful information. In some spreadsheets I have used arrays
(Excel 2003), sometimes one or more of the array formulas do not
correctly update. Then I need to edit it (crtl-shift-enter) and then the
result is correctly displayed. If the same worksheet is used in Excel
2007 then the problem is more serious: most of the array formulas do not
update correctly. Ctrl-shift-enter also solves the issue in Excel 2007.
This may give a hint on where to look for the solution for your problem
(and hopefully for mine also...)




downwitch;649148 Wrote:

After exhausting every option in terms of reworking my code and making
sure there was nothing in there that was causing a problem, I decided
to reproduce the error in the simplest possible setup, in hopes of
getting a little (more) help here. You can now see what I see: create
a fresh blank workbook, add a module to it, and paste this code in:

'---BEGIN CODE---
Sub Test_Me1()
Create_Test
Crash_Test
End Sub

Sub Test_Me2()
Create_Test
Crash_Test2
End Sub

Sub Create_Test()
Dim wks As Excel.Worksheet
Set wks = ThisWorkbook.Worksheets(1)
With wks
.Range("$B$2").Value = "'2010"
.Range("$C$2").Value = "'2011"
.Range("$D$2").Value = "'2012"
.Range("$E$2").Value = "'2013"
.Range("$G$2").Value = "RowTotal"
.Parent.Names.Add Name:="Sheet1!TableWks", RefersTo:="=Sheet1!
$B$3:$E$11"
.Parent.Names.Add Name:="Sheet1!Wks_Total", RefersTo:="=Sheet1!
$G$3:$G$11"
.Range("Wks_Total").FormulaArray = _

"=SUM(OFFSET(TableWks,ROW(Wks_Total)-3,0,1,COLUMNS(TableWks)))"
End With
Set wks = Nothing
End Sub

Sub Crash_Test()
Dim wks As Excel.Worksheet
Set wks = ThisWorkbook.Worksheets(1)
With wks
.Range("TableWks").Value = 0
.Range("$B$4").Value = 31
.Range("$C$5").Value = 12
.Range("$D$3").Value = 9
.Range("$E$5").Value = 15
.Range("$B$6").Value = 121
.Range("$C$6").Value = 19
.Range("$D$7").Value = 6
.Range("$D$8").Value = 222
.Range("$E$9").Value = 43
End With
Set wks = Nothing
End Sub

Sub Crash_Test2()
Dim rng As Excel.Range
Set rng = ThisWorkbook.Worksheets(1).Range("TableWks")
With rng
.ClearContents
.Value = 0
.Cells(2, 1).Value = 31
.Cells(3, 2).Value = 12
.Cells(4, 3).Value = 9
.Cells(5, 3).Value = 15
.Cells(4, 1).Value = 121
.Cells(5, 2).Value = 19
.Cells(6, 3).Value = 6
.Cells(7, 3).Value = 222
.Cells(8, 4).Value = 43
End With
Set rng = Nothing
End Sub
'---END CODE--

Then all you have to do, from the immediate window, is run Test_Me1 or
Test_Me2 (or, if you want, run Create_Test and Crash_Test or
Crash_Test2 separately, if you feel like fiddling in between) to see
the error result I'm getting. Make one manual data change or hit F9
once user control returns--invoke volatility--and you'll see the error
vanish before your very eyes. Note that this occurs without any UDFs
at all, and without altering .ScreenUpdating, .Calculation, etc.

Any help on what is causing this formula to fail would be really,
really appreciated, as I am now into double-digit hours trying to
figure this out. If I don't hear back here I will be (cross-)posting
this to the worksheet functions forum, as it now appears to straddle
VBA and pure Excel.


--
Rmorrone
------------------------------------------------------------------------
Rmorrone's Profile: 1515
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180681

Microsoft Office Help