Copy/Paste Formulas is multiples of 10 rows causes crash on following calulate

marc81

New Member
Joined
Aug 3, 2017
Messages
16
Hi,

I have a macro and part of it involves copying a range of formulas and pasting it down rows while in a loop while updating a progress bar. When I am doing this row by row the macro works perfectly, but when I try to do it in batches of 10 rows the code works fine but when the macro gets to the next 'calculate' after this section of code excel crashes... basically whenever a calculate/save/calculation automatic code is found after this code finishes it crashes. Any ideas what I am doing wrong?? Examples below are firstly my line by line code which causes no issues, and then my batches of 10 code that causes the crash at any following calulate. Hope this makes sense... I am pretty new to VBA codeing.

:::working code:::

Dim KeyedDataRange As Long
Dim OriginalUsedDataRange As Long
Dim KeyedDataPostRemoval As Long
Dim RowProgress As Long
Dim CalcRowStart As Long
Dim CalcRowEnd As Long
Dim CurrentProgress As Double
Dim BarWidth As Long
Dim ProgressPercentage As Double
Dim CalcRowFinal As Long

RowProgress = 1
CalcRowStart = 4
CalcRowFinal = KeyedDataRange + 2

Call InitProgressBar

Do While CalcRowStart < CalcRowFinal + 1

Range("A2:L2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & CalcRowStart & ":L" & CalcRowStart).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A" & CalcRowStart & ":L" & CalcRowStart).Calculate


CurrentProgress = RowProgress / (CalcRowFinal - 3)
BarWidth = Progress.Border.Width * CurrentProgress
ProgressPercentage = Round(CurrentProgress * 100, 0)

Progress.Bar.Width = BarWidth
Progress.Text.Caption = "STEP 1 of 2: Used Data Calculations - " & ProgressPercentage & "% Complete"

DoEvents

CalcRowStart = CalcRowStart + 1
RowProgress = RowProgress + 1

Loop

Unload Progress

:::code which causes excel crash on next calculate:::

Dim KeyedDataRange As Long
Dim OriginalUsedDataRange As Long
Dim KeyedDataPostRemoval As Long
Dim RowProgress As Long
Dim CalcRowStart As Long
Dim CalcRowEnd As Long
Dim CurrentProgress As Double
Dim BarWidth As Long
Dim ProgressPercentage As Double
Dim CalcRowFinal As Long

RowProgress = 1
CalcRowStart = 4
CalcRowEnd = 13
CalcRowFinal = KeyedDataRange + 2

Call InitProgressBar

Do While CalcRowStart < CalcRowFinal + 1

Range("A2:L2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A" & CalcRowStart & ":L" & CalcRowEnd).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A" & CalcRowStart & ":L" & CalcRowEnd).Calculate


CurrentProgress = RowProgress / (CalcRowFinal - 3)
BarWidth = Progress.Border.Width * CurrentProgress
ProgressPercentage = Round(CurrentProgress * 100, 0)

Progress.Bar.Width = BarWidth
Progress.Text.Caption = "STEP 1 of 2: Used Data Calculations - " & ProgressPercentage & "% Complete"

DoEvents

CalcRowStart = CalcRowStart + 10
CalcRowEnd = CalcRowEnd + 10
RowProgress = RowProgress + 10

Loop

Unload Progress
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi,

I am not sure how your code is even entering the loop as CalcRowStart=4 doesn't look like it will ever be < CalcRowEnd=0+2 at the start.

However, in your second code you are adding +10 to both, therefore if one is less than the other on entering the loop it will remain so and your loop will continue until you exceed memory.

Hope this helps,

Eric.
 
Upvote 0
Thanks for the reply Eric.. I should point out that there is code before this.. KeyedDataRange is set earlier as a count of the data on the sheet... so < CalcRowEnd=0+2 is actually more likely to be something like < CalcRowEnd=800+2.

Like I say the above does work, it's just that following the above code anything that involves a calculation after this in the macro just crashes... but fine with the first lot of code I posted.
 
Upvote 0
Hi,

It still remains that in the second piece of code you have posted, you are adding +10 to both CalcRowStart & CalcRowEnd, therefore if one is less than the other on entering the loop it will remain so and your loop will continue until you exceed memory.

Code:
CalcRowStart = CalcRowStart + 10
CalcRowEnd = CalcRowEnd + 10

Eric,
 
Upvote 0
The loop ends fine as it is when CalcRowStart exceeds CalcRowFinal (which is a static number).. CalcRowEnd has no affect in this. As mentioned the code above works fine and it exits the loop properly etc... it's just any code after this that requires calculation then crashes excel... whereas it's fine without the above code before it. Could it be a cache issue?
 
Upvote 0
Interestingly enough I can use the same code further through the macro on formulas that are on another sheet and it causes no crashing issues... mystery.
 
Upvote 0
Hi,

Sorry, my mistake, misread them.

Well, I am out of ideas & unless the file is massive or the formulas being copied are seriously complex I can't see wy excel would crash.

Maybe try saving the file before doing further calcs?

Eric.
 
Upvote 0
Unfortunately a save forces a recalc so even putting a save directly after this section of code crashes excel.
 
Upvote 0
So I've amended my formulas so that rather than looking at full column ranges they now look at a fixed range (I've had to do this as indirect off a count of the data as the data range changes every time) and it works! It clearly didnt like my formulas.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top