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

[marc81]

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

 

[Eric Golf]

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.

 

[marc81]

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.

 

[Eric Golf]

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.

CalcRowStart = CalcRowStart + 10
CalcRowEnd = CalcRowEnd + 10

Eric,

 

[marc81]

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?

 

[marc81]

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.

 

[Eric Golf]

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.

 

[marc81]

Unfortunately a save forces a recalc so even putting a save directly after this section of code crashes excel.

 

[marc81]

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.

 

[Eric Golf]

Glad you got it sorted,

Eric