有没有比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