General discussion

Locked

Excel formula won't update

By wbryanfamily ·
I have written an Excel function to sum the values from the cells surrounding a selected cell. For example if I want to sum the values found in the cells immediatly below my selection and then insert the value into the selected cell I enter my function into the selected cell as "=GroupSum(Row(),Column(), 1,0)".
The function works when I first insert it, but subsequently when I change any of the values in the fields used by the calculation within the function, the resulting function value is not updated automatically.

I have tried adding the Worksheets(1).Calculate method to the SelectionChange event on this specific worksheet, but that seems to have no effect. My workaround for now is to enter all of my updated values into the spreadsheet first and then hit the CTRL-ALT-SHIFT-F9 keys and update all of the calculations on the worksheet at once. Is there any other way to get this value to update automatically? I don't mind if all of the calculations on the page are updated at once. I just want something that will update the calculations without additional user intervention.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

should be fine

by frostbite In reply to Excel formula won't updat ...

without explicitly firing the calculate method on selection change.

if you don't mind posting the code for your function, I can make a more in-depth answer to this.

Collapse -

Solution

by wbryanfamily In reply to should be fine

I found the resolution on a different web site. The answer was to set the Application.Volatile property on the function.

In case you still want to see what I am doing here is the entire function code that now works.

Function GroupSum(r As Integer, c As Integer, ro As Integer, co As Integer)
'==============================================='
'This function is used to insert a sum of the cells surrounding the selected cell
'William Bryan 4/14/2006
'r is the starting row number
'c is the starting column number
'ro indicates the row offset.
'If it is positive you want to move the selection down by the number of cells indicated.
'co indicates the column offset.
'If it is positive you want to move the selection right by the number of cells indicated.
'This function works best if you enter it as:
' GroupSum(Row(),Column(), #,#)
'==============================================='
Application.Volatile

Dim s As Worksheet
Dim ra As Range
Dim CellValue As Long
Dim x As Long

x = 0
CellValue = 0
Set s = Application.ActiveSheet
While Not IsEmpty(s.Cells(r + ro, c + co))
'need to select the next cell indicated by the ro and co values
r = r + ro
c = c + co
Set ra = s.Cells(r, c)
CellValue = ra.Value
x = x + CellValue
Wend


GroupSum = x

End Function

Collapse -

another solution

by joshjskelton In reply to Solution

You may have to change your settings in Excel from manual cell formula update to automatic.

I was having the same problem until someone pointed out that if you go to Tools>>Options>>Calculation tab>> and change the Calculation selection from Manual to Automatic. It's worth checking out.

Collapse -

Automatic cell update

by lowell_barton In reply to another solution

I also was having this problem. . . . .
In the calculation tab,"Automatic" was chosen however the automatic feature wasn't working.I switched to manual and OK'ed it. Then I switched back to automatic and now the automatic function works fine.Thanks Josh for your clue to this Excel problem>

Collapse -

Another Solution

by Mmurphy18 In reply to Automatic cell update

Application.Volatile might work but if you were to put Application.Calculation = xlAutomatic into your Workbook_Open() sub, it will automatically turn live calculations on for anyone else who is opening the sheet.

Collapse -

Cheers!

by djplee In reply to another solution

Was having a similar problem...you saved me hours of searching for the reason. Many thanks.

Collapse -

Excel 2010

Formulas Tab, Calculation Options,

Collapse -

excel 10

by flyrv6 In reply to Excel formula won't updat ...

I want to sum a row of numbers such as 6h+8(t)+10(ot). The numbers I want to count but leave the letters in the cells as text.

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Forums