Using macro to add 5 cells together into cell and then add this cell to another cell - -gives wrong value

[gyro2222]

I'm trying to add time spent m-f on a project.
I randomize the data for each day, then add the cells for m-f together and then add this value to a cell which keeps a running total of the weeks.
The macro does this, adds 7 days to the week and repeats.
The macro works well until I try to get the week summary values to replace the summary of the prior weeks.
the odd thing is that if I use the portion to add a cell to the summary cell which is not part of the randomize cell operation, it works OK.
here's the code. I put a 5-second pause in to see what is happening. It appears after it randomizes and produces a sum for the week, the value changes when I try to add it to the prior weekly summary.
it also prints the individual weeks (I've commented that out, but when active, it provides the wrong value)
hope you can help.
Thanks,
Hal

' ctrl T
'
' Keyboard Shortcut: Ctrl+t
'
Range("L3").Select
Selection.Copy
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
Application.Wait (Now + TimeValue("0:00:07"))
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("ab4") = Range("ab4") + Range("i4")
Range("ab5") = Range("ab5") + Range("i5")
Range("ab6") = Range("ab6") + Range("i6")
.Range("ab7") = Range("ab7") + Range("i7")

'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[jasonb75]

If you have a formula that is generating random numbers in a cell then the random values will change several times while your code is running, each time a cell is changed by it triggers recalculation.

To prevent this, add the line Application.Calculation = xlManual at the start of your code and Application.Calculation = xlAutomatic at the end.

Not sure if this will fix the problem entirely without having the exact same setup for testing, but hopefully it will go somewhere towards it.

See the link below for info on volatile functions (which includes rand and randbetween), any actions listed as a cause of recalculation apply whether done manually or by vba.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

 

[gyro2222]

Hi Jason
it doesn't seem to work:
' Keyboard Shortcut: Ctrl+t
'
Application.Calculation = xlManual
Range("L3").Select
Selection.Copy
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
Application.Wait (Now + TimeValue("0:00:02"))
Range("ab4") = Range("i4") + Range("ab4")
Range("ab5") = Range("i5") + Range("ab5")
Range("ab6") = Range("i6") + Range("ab6")
'Range("ab7") = Range("i4") + Range("ab7")
'Range("ab7") = Range("ab7") + Range("i4")
Application.Calculation = xlAutomatic
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

here's a link to my dropbox folder with the Excel sheet.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[jasonb75]

Can't open the file to test anything, it's password protected.

 

[gyro2222]

Can't open the file to test anything, it's password protected.

sorry. here it is again.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[jasonb75]

What happens if you keep Application.Calucluation =xlManual and Application.Calucluation =xlAutomatic and comment out Calculate in the middle of the code?

It appears to fix the problem you're having, but it could create other problems that I'm not seeing.

 

[gyro2222]

I'm unclear on your suggestion. I did a short video of the problem.
Excel.mp4
Hope you can figure it out.
Thanks,
Hal

 

[jasonb75]

This is what I meant, does this fix the problem with causing others?

Sub TimeshhetAdd1weekRecalculateandprint()
'
' TimeshhetAdd1weekRecalculateandprint Macro
' ctrl T
'
' Keyboard Shortcut: Ctrl+t
'
   Application.Calculation = xlManual
    Range("L3").Select
    Selection.Copy
    Range("K3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    'Calculate   ' **** This line is now disabled ****
    Application.Wait (Now + TimeValue("0:00:02"))
           Range("ab4") = Range("i4") + Range("ab4")
    Range("ab5") = Range("i5") + Range("ab5")
    Range("ab6") = Range("i6") + Range("ab6")
    'Range("ab7") = Range("i4") + Range("ab7")
    'Range("ab7") = Range("ab7") + Range("i4")
   Application.Calculation = xlAutomatic
      'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[gyro2222]

It worked. Thanks so much!!

Thanks so much.