My VBA code is extremely slow when running-CodePudding

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 RTB_justering()
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? Please bare in mind that I started today with VBA so I am a noobie.

CodePudding user response：

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

CodePudding user response：

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