如何使用范围和字符串比较来减少代码的运行时间 [英] How to reduce runtime for code using ranges and string comparisons

问题描述

我有以下代码，它完全是我需要的，但是，循环需要太长时间才能运行（3分钟+）。我是VBA的新手，所以我不能确定1）最好的选择是2）如何使用适当的语法替代，并让我的代码运行无瑕疵。谢谢！

I have the following code and it does exactly what I need it to do however, the loop takes far too long to run (3 minutes +). I am new to VBA so I am not exactly sure 1) what the best alternative is 2) how to use the proper syntax for that alternative and have my code run flawlessly. Thanks!

Dim i As Integer
For i = 2 To 13000

If Sheets("Sheet1").Range(Cells(i, 3), Cells(i, 3)) = "Police" 
    And Sheets("Sheet1").Range(Cells(i, 14), Cells(i, 14)) = "Bi-wkly Uniform Pay" Then _
Sheets("Sheet1").Range(Cells(i, 3), Cells(i, 3)) = "Police - Uniform"

Next i

推荐答案

一个循环很慢。一个更好的方法是将数据复制到数组中的 Variant ，数组循环，然后将结果复制到表格

Accessing a sheet within a loop is very slow. A better approach is to copy the data into a Variant, array loop over the array and then copy the results back to the sheet

这样的东西：

Sub Demo()
    Dim i As Long
    Dim datCol3 As Variant
    Dim datCol14 As Variant

    With Sheets("Sheet1")
        ' Copy data into a Variant Array
        datCol3 = .Range(.Cells(1, 3), .Cells(13000, 3)).Formula
        datCol14 = .Range(.Cells(1, 14), .Cells(13000, 14)).Value
        ' Loop over the array
        For i = 2 To 13000
            If datCol3(i, 1) = "Police" And datCol14(i, 1) = "Bi-wkly Uniform Pay" Then
                datCol3(i, 1) = "Police - Uniform"
            End If
        Next
        'Return the results to the sheet
        .Range(.Cells(1, 3), .Cells(13000, 3)).Formula = datCol3
    End With
End Sub

这篇关于如何使用范围和字符串比较来减少代码的运行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！