0

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.

Dan
  • 45,079
  • 17
  • 88
  • 157
andrescpacheco
  • 604
  • 1
  • 8
  • 26
  • 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](http://stackoverflow.com/a/10717999/2143262) – Kazimierz Jawor Jun 25 '13 at 14:59
  • 1
    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 Jun 25 '13 at 15:09

1 Answers1

1

As KazJaw said... try:

Range("B" & 3 + j).Copy Destination:= Range("H" & 3 + i + j * 4)
Ed Heywood-Lonsdale
  • 108
  • 6
  • +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. – David Zemens Jun 25 '13 at 15:18