Set WB = Thisworkbook
Set wk = WB.Sheets("Final")
Set ws = WB.Sheets("OIT-Temp")

irow = wk.Range("B65000").End(xlUp).Row 'Find last row

For i = 2 To irow Step 1
If not wk.Cells(i, 2).FormulaR1C1 = "#N/A" Then
wk.cells(i, 2).copy ws.Cells(i, 2)
wk.cells(i,3).Value = 128

Else

wk.Cells(i, 3).Value = 198

End if
Next i

I'm here facing too much of lag while running this code and unable to understand what's wrong with my code

CodePudding user response：

Some general points:

1. Indent your code.
2. Give your variables meaningful names.
3. Normal to write positive code block in if/else first.
4. I'm not sure how you're doing an xlUp on an entire row.

For this specific problem, reading and writing cells is computationally expensive. You can remove a lot of this by reading and writing data in bulk and working with your data in arrays in memory.

Try this and see if it speeds things up.

Sub FastReadWrite()
'   Set the range references.
    With ThisWorkbook
        With .Sheets("Final")
            Dim maxRow As Long
            maxRow = .Range("B65000").End(xlUp).Row
            
            Dim finalData As Range
            Set finalData = .Range("B2:C" & maxRow)
        End With
        Dim oitTemData As Range
        Set oitTemData = .Sheets("OIT-Temp") _
                         .Range("B2:C" & maxRow)
    End With
        
'   Read the data from the ranges into memory.
    Dim fArray() As Variant: fArray = finalData.Formula2R1C1
    Dim tArray() As Variant: tArray = oitTemData.Formula2R1C1
        
'   Main logic loop.
    Dim i As Long
    For i = 1 To UBound(fArray, 1)
        If fArray(i, 1) = "#N/A" Then
            fArray(i, 2) = 198
        Else
            tArray(i, 1) = fArray(i, 1)
            fArray(i, 2) = 128
        End If
    Next i
    
'   Write the data to the ranges.
    finalData.Formula2R1C1 = fArray
    oitTemData.Formula2R1C1 = tArray
End Sub

CodePudding user response：

Please, try the next adapted code. It uses arrays, mostly working in memory and drops the processed arrays at once. That's why it should be very fast:

Sub speedUpCode()
 Dim WB As Workbook, wk As Worksheet, ws As Worksheet, iRow As Long, arr, arrCopy, i As Long
 
 Set WB = ThisWorkbook
 Set wk = WB.Sheets("Final")
 Set ws = WB.Sheets("OIT-Temp")

 iRow = wk.Range("B" & wk.rows.count).End(xlUp).row 'Find last row
 arr = wk.Range("B2:C" & iRow).Value2      'place the range in an array for faster iteration
 arrCopy = ws.Range("B2:B" & iRow).Value2  'place the range in an array for faster iteration

 For i = 1 To UBound(arr)
    If Not CStr(arr(i, 1)) = "Error 2042" Then ''#N/A
        arrCopy(i, 1) = arr(i, 1)
        arr(i, 2) = 128
    Else
        arr(i, 2) = 198
    End If
 Next i
 'drop the processed array values:
 wk.Range("B2").Resize(UBound(arr), 2).Value2 = arr
 ws.Range("B2").Resize(UBound(arrCopy), 1).Value2 = arrCopy
End Sub