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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
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,003
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,003
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,003
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,117
Messages
5,546,038
Members
410,721
Latest member
adi772
Top