Is Excel unreliable ?
 

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/

Is Excel unreliable ?
 

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

"DesCF" wrote:


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/

Is Excel unreliable ?
 

Do you have your calculation set to manual? Check Tools - Options
-Calculation

"DesCF" wrote:


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/

Is Excel unreliable ?
 

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DesCF" wrote in message news:op.ts8qz901upgxg0@descstar...
|
| 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/

Is Excel unreliable ?
 

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











On Fri, 01 Jun 2007 13:47:00 +0100, Mike H
wrote:

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

"DesCF" wrote:


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/




--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Is Excel unreliable ?
 

You need to add volatile to your function


--
Regards,

Peo Sjoblom


"DesCF" wrote in message news:op.ttgp7tizupgxg0@descstar...

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











On Fri, 01 Jun 2007 13:47:00 +0100, Mike H
wrote:

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

"DesCF" wrote:


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/




--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Is Excel unreliable ?
 

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))


In article <op.ttgp7tizupgxg0@descstar, DesCF wrote:

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