Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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/
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,501
Default 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/

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,355
Default 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/

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,440
Default 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/


  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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/


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default 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/


  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4,624
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Scrolling in Excel unreliable Peter Excel Discussion (Misc queries) 0 June 21st 06 02:30 AM


All times are GMT +1. The time now is 11:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"