excel - 有没有比 FOR EACH 更快的方法来使用 VBA 设置单元格值

Question

有时，此范围可能是数万行用于验证。目前，为了将验证设置为选择的正确值，我正在执行 For Each。以下是设置验证的部分代码。

With BCS.Range("AT2:AT" & Lrow).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=BStr
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

BCS.Range("AT2:AT" & Lrow).Value = "1"  

ThisWorkbook.Sheets("est_temp").Range("A1:A" & Lrow2).Copy
ThisWorkbook.Sheets("B_C_I").Range("AO2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.DisplayAlerts = False
ThisWorkbook.Sheets("est_temp").Delete
Application.DisplayAlerts = True

If Lrow > 2 Then
    For Each c In BCS.Range("AO2:AO" & Lrow)
        With c
            If c.Value = "AA" Then
                Range("AT" & c.Row).Value = "Std % 1"
            ElseIf c.Value = "BB" Then
                Range("AT" & c.Row).Value = "Std % 2"
            Else
                Range("AT" & c.Row).Value = "1"
            End If
        End With
    Next c
End If

出于显而易见的原因，当您必须经过 25000 多行时，这需要相当长的时间。有比 For Each 更快的方法吗？

Answer 1

正如@Rory 所说 - 这会更快:

Dim arr, r As Long

If Lrow > 2 Then
    arr = BCS.Range("AO2:AO" & Lrow).Value 'get all the data in an array
    For r = 1 to UBound(arr, 1)
        Select Case arr(r, 1)
            Case "AA": arr(r, 1) = "Std % 1"
            Case "BB": arr(r, 1) = "Std % 2"
            Case Else: arr(r, 1) = "1"
        End Select
    Next r
    BCS.Range("TO2:TO" & Lrow).Value = arr 'write back to sheet
End If