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

gyro2222

New Member
Joined
Dec 6, 2014
Messages
7
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,018
Office Version
  1. 2019
Platform
  1. Windows
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.

 

gyro2222

New Member
Joined
Dec 6, 2014
Messages
7
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,018
Office Version
  1. 2019
Platform
  1. Windows
Can't open the file to test anything, it's password protected.
 

gyro2222

New Member
Joined
Dec 6, 2014
Messages
7

ADVERTISEMENT

Can't open the file to test anything, it's password protected.
sorry. here it is again.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,018
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 6, 2014
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,018
Office Version
  1. 2019
Platform
  1. Windows
This is what I meant, does this fix the problem with causing others?
VBA Code:
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

New Member
Joined
Dec 6, 2014
Messages
7
It worked. Thanks so much!!
fix it.jpg
Thanks so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,169
Members
410,775
Latest member
alal1030
Top