Speed up For Loop by using an Array-CodePudding

I'm looking to speed up a For Loop (as per code below) by incorporating the use of an Array. Would really appreciate some advice on how to do this:

Sub DetectedCheck()
'counts rows in sheet 1 and 2.
With Sheets(1)
reconrows = .Range("a" & .Rows.Count).End(xlUp).Row
End With
 
 'Checks that the new data has both an assigned and detected role and adds "No Issue" to column Q if valid.
For i = 2 To reconrows
        If ActiveWorkbook.Sheets(1).Range("J" & i).Value <> "Not Found" And ActiveWorkbook.Sheets(1).Range("K" & i).Value <> "" Then
            ActiveWorkbook.Sheets(1).Range("S" & i).Value = "No Issue"
        End If
Next i
 
 
End Sub

CodePudding user response：

Please, try the next way:

Sub DetectedCheck()
 Dim sh As Worksheet, reconRows As Long, arrJK, arrS, i As Long

 Set sh = Sheets(1)
  reconRows = sh.Range("a" & sh.rows.count).End(xlUp).row
  arrJK = sh.Range("J2:K" & reconRows).value
  arrS = sh.Range("S2:S" & reconRows).value
  
 'Checks that the new data has both an assigned and detected role and adds "No Issue" to column Q if valid.
 For i = 1 To UBound(arrJK)
        If arrJK(i, 1) <> "Not Found" And arrJK(i, 2) <> "" Then
           arrS(i, 1) = "No Issue"
        End If
 Next i
 sh.Range("S2").Resize(UBound(arrS), 1).value = arrS
End Sub

But in the code comment you mention "No Issue" to column Q" and in your code you use S:S column. Please, adapt if the return must be done in Q:Q.

CodePudding user response：

Want to test this method and see the speed of looping with arrays compared to rows?

 Dim timmy, i As Long, rc As Long, arr1, arr2, arr3
 timmy = Timer
    With Sheets(1)
    rc = .Range("A" & Rows.Count).End(xlUp).Row
        arr1 = .Range("J2:J" & rc).Value
        arr2 = .Range("K2:K" & rc).Value
        ReDim arr3(1 To UBound(arr1), 1 To 1)
        For i = 1 To UBound(arr1, 1)
            If arr1(i, 1) = "Not Found" And IsEmpty(arr2(i, 1)) Then
                arr3(i, 1) = ""
            Else
                arr3(i, 1) = "No Issue"
            End If
        Next i
        .Range("S2:S" & rc).Value = arr3
    End With
    Debug.Print "Loopy", Timer - timmy