MS Excel crashes when running VBA code

Question

I'm currently having a problem with my code and i can't find the proper solution for it. The input of this code is 8760 registries with values in from of it. For example in column B there is the 8760 values that correspond to the number of hours in a year and in front of it i have the value for each hour. as i want to have that values in each 15 minutes i need to divide the value by 4 and paste it. So i have in column B the hours, in the column C the value and i want to have in column D the the value divided by 4 so i will have 8760*4 registries.

Option Explicit
Sub test()

Dim i, j As Long
Dim valor As Long


For j = 0 To 8759
    For i = 1 To 4
       valor = Range("C" & 3 + j).Value
       Range("B" & 3 + j).Select
       Selection.Copy
       Range("H" & 3 + i + j * 4).Select
       ActiveSheet.Paste
       Range("I" & 3 + i + j * 4) = valor / 4
   Next
Next

End Sub

It crashes my program after a while. It runs ok till the crash.

I think you need to eliminate all Select, Selection and other Active... stuff which usually slow down and decrease efficiency. As a result it seems that macro crashes after a while... You should find this link very useful — Kazimierz Jawor
– Kazimierz Jawor, Commented Jun 25, 2013 at 14:59
You should definitely look into doing this differently. Based upon what you're doing, I think it's likely you could do it with formulas. As an aside, if it wasn't crashing your code would throw an error every iteration of 3 + i + j * 4 once j >= 8191 — Daniel
– Daniel, Commented Jun 25, 2013 at 15:09

Ed Heywood-Lonsdale · Accepted Answer · 2013-06-25 15:19:47Z

1

As KazJaw said... try:

Range("B" & 3 + j).Copy Destination:= Range("H" & 3 + i + j * 4)

edited Jun 25, 2013 at 15:19

answered Jun 25, 2013 at 15:02

Ed Heywood-Lonsdale

1086 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Zemens Over a year ago

+1. One line of code instead of 4 lines of code :) Same method would work with .Copy instead of .Cut depending on OP's requirements.

Collectives™ on Stack Overflow

MS Excel crashes when running VBA code

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related