Is Excel unreliable ?

  • Thread starter Thread starter DesCF
  • Start date Start date
D

DesCF

Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e.
it just doesn't do it or gives wrongs answers. The only way to get the
right answers is to drag down the calculated cells again and then press F9
/ Shift+F9. Before going into more detail I just wanted to know if I am
encountering a common problem ?
 
I don't understand what you mean by 'drag down the calculated cell'

Also could we see the particular formula that Excel is getting wrong.

I'm not aware that this is a general problem but others may differ.

Mike
 
Look here:

http://xldynamic.com/source/xld.xlFAQ0024.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e.
| it just doesn't do it or gives wrongs answers. The only way to get the
| right answers is to drag down the calculated cells again and then press F9
| / Shift+F9. Before going into more detail I just wanted to know if I am
| encountering a common problem ?
|
|
|
|
| --
| Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
 
Here is the function I am using. It is used in sheet 2 and references
cells in sheet 1. It is used twice in two adjacent cells and the results
are then summed in a third adjacent cell. In a forth adjacent cell the
figure in the third adjacent cell is added to another figure taken from
the immediately preceding row in the forth adjacent cell.


Public Function sumValues(strName As String, strType As String) As Currency

Dim intI As Integer
Dim intJ As Integer
Dim curC As Currency

Select Case strType
Case "D"
intJ = 7
Case "C"
intJ = 8
End Select

With Sheet1
For intI = 1 To 10000
If .Cells(intI, 3) = strName Then
curC = curC + .Cells(intI, intJ)
End If
Next
End With

sumValues = curC

End Function
 
You need to add volatile to your function


--
Regards,

Peo Sjoblom



Here is the function I am using. It is used in sheet 2 and references
cells in sheet 1. It is used twice in two adjacent cells and the results
are then summed in a third adjacent cell. In a forth adjacent cell the
figure in the third adjacent cell is added to another figure taken from
the immediately preceding row in the forth adjacent cell.


Public Function sumValues(strName As String, strType As String) As Currency

Dim intI As Integer
Dim intJ As Integer
Dim curC As Currency

Select Case strType
Case "D"
intJ = 7
Case "C"
intJ = 8
End Select

With Sheet1
For intI = 1 To 10000
If .Cells(intI, 3) = strName Then
curC = curC + .Cells(intI, intJ)
End If
Next
End With

sumValues = curC

End Function
 
You haven't told VBA that sumValues depends on Sheet1!A1:A10000, so it
doesn't know to recalculate sumValues when a value in that range changes.

Either add

Application.Volatile

at the beginning of the function, or include the range as an argument to
the function.

However, I'd think you could do much faster and more efficient with a
built-in function:

=SUMIF(Sheet1!$C$1:$C$10000, strName, IF(strType="D",
Sheet1!$G$1:$G$10000, Sheet1!$H$1:$H$10000))
 
Back
Top