My VBA code is extremely slow when running

Question

Basically I want to loop through column H (with 430 rows) and if its value is "Tera" then I want to change the value of the cell in column E (i.e. 6th column) with the value in column F (7th column).

Sub R_Adj()
Sheets("Sheet5").Select
temp = 7
For Each i In Worksheets("Sheet5").Range("H8:H430").Cells
temp = temp + 1
Set column_to = Worksheets("Sheet5").Cells(temp, 6)
Set column_from = Worksheets("Sheet5").Cells(temp, 7)
If i.Value = "Tera" Then column_to.Value = column_from.Value
Next
End Sub

The running time is about 10 seconds which is too much in my opinion for such an easy algorithm. Does anybody have any suggestions to improve the performance and running time of this code?

I tried your code in a new worksheet and it finishes instantly. So maybe there's something wrong with your worksheet — Charles, Feb 13 '22 at 15:59
You can always try to turn off ["Excel functionality"](https://stackoverflow.com/questions/47089741/how-to-speed-up-vba-code/47092175#47092175) — Storax, Feb 13 '22 at 18:47
Are you aware that column `E` is the `5th` column and column `F` is the `6th` column? You should always reference the workbook, where this is happening, too, and use variables e.g. `Dim wb As Workbook: Set wb = ThisWorkbook` next line `Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet5")`. Now you can safely continue using `ws` instead of `Worksheets("Sheet5")` or even worse `Sheets("Sheet5")`. You don't need to [select](https://stackoverflow.com/q/10714251) anything. `i` is kind of reserved for counters (whole numbers) so you should use something like `cell` as a one-cell variable instead. — VBasic2008, Feb 13 '22 at 22:25
As i mentioned in the question, I started with VBA yesterday and have zero experience with it. Could you explain what dim wb as workbook: ... does? — StVnilor, Feb 14 '22 at 11:41

score 0 · Answer 1 · answered Feb 13 '22 at 17:35

Use an array.

Option Explicit
Sub RTB_justering()

    Dim rng As Range, ar, i As Long, n As Long
    With Sheets("Sheet5")
        Set rng = .Range("E8:H430")
        ar = rng.Value2
        For i = 1 To UBound(ar)
            If ar(i, 4) = "Tera" Then 'H
                ar(i, 1) = ar(i, 2) ' F->E
                n = n + 1
            End If
        Next
        rng.Value2 = ar
    End With
    MsgBox n & " cells changed", vbInformation
    
End Sub

Xavier Junqué · Answer 2 · 2022-02-13T17:15:41.037

-1

Perhaps the following may improve running time a bit:

    Sub RTB_justering()
    Dim sh5 As Worksheet
    Dim rng As Range
    Set sh5 = Worksheets("Sheets5")
    sh5.Select
    For Each rng In sh5.Range("H8:H430")
        If rng.Value2 = "Tera" Then
            '    value2 in Column E <-- value2 in Column F
            rng.Offset(0, -3).Value2 = rng.Offset(0, -2).Value2
        End If
    Next
End Sub

edited Feb 13 '22 at 17:15

answered Feb 13 '22 at 17:08

Xavier Junqué

350
2
5

My VBA code is extremely slow when running

2 Answers2