0

I have a total of 800 rows. While the code is running, excel freezes and is completed in almost 1 minute. Where do you think this problem arises? Does the same code run fast and smoothly on your computer? Do you have the same problems while the code is running?

Sub counterfunc()
 Dim i, counter As Integer
 For i = 2 To Sayfa3.Range("A:A").End(xlDown)

 Sayfa3.Cells(i, "J").Value = Sayfa3.Cells(i, "A").Value
 counter = counter + 1
Next  

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
sabcan
  • 407
  • 3
  • 15
  • 4
    `Sayfa3.Range("A:A").End(xlDown)`... no, that's not doing what you think it is. But more importantly, why are you looping in the first place? – BigBen Mar 18 '21 at 12:49
  • 3
    `Sayfa3.Range("A:A").End(xlDown)` takes the value of the last populated row before a blank row in A – Warcupine Mar 18 '21 at 12:49
  • 2
    [The correct way to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), [Use Long instead of Integer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Mar 18 '21 at 12:51
  • 2
    Also worth noting that `Dim i, counter As Integer` is also not doing what you think it does, assigning a `Variant/Double` variable to `i` by "guessing". Either way, try to get into the habit of using `Long` type variable for loops (when a loop is required) > `Dim i as Long, Counter as Long` – JvdV Mar 18 '21 at 12:57
  • 1
    Why are you even bothering with VBA, this is a simple formula. – SJR Mar 18 '21 at 13:00

1 Answers1

2

If you were going to loop, this would get it done:

Sub counterfunc()
 Dim i As Long
 Dim LR As Long
 LR = Sayfa3.Cells(Rows.Count, 1).End(xlUp).Row
 
 For i = 2 To LR

    Sayfa3.Cells(i, "J").Value = Sayfa3.Cells(i, "A").Value

 Next i

End Sub

However, as stated, why loop? You could simply copy/paste the column (or pastespecial if formatting is an issue)

Sub counterfunc2()
    Sayfa3.Columns("A").Copy Sayfa3.Columns("J")
    
End Sub
Darrell H
  • 1,876
  • 1
  • 9
  • 14