循环遍历多列:优化

Question

我对宏/VBA完全陌生。我们有一个需求，我需要对多个列检查不同的条件，如果满足条件，则更新最后一列。

我能够对它进行编码(复制和编辑)，并且它可以很好地对抗小no。的记录。问题是，我的文件有20000+记录，现在VBA需要大约40-45分钟来更新"Call Down Start date_custom";列。

是否有优化代码或使其运行得更快的方法?

Sub Macro3_Call_down_start_date_custom()
Dim i As Long
With ActiveSheet.ListObjects(1)
For i = 1 To .DataBodyRange.Rows.Count
If .ListColumns("Activation Status").DataBodyRange(i).Value = "No" And _
.ListColumns("Call Down Start Date").DataBodyRange(i).Value = "" Then
.ListColumns("Call Down Start Date_CUSTOM").DataBodyRange(i).Value = .ListColumns("Date Added").DataBodyRange(i).Value + 3

ElseIf .ListColumns("Activation Status").DataBodyRange(i).Value = "Yes" And _
.ListColumns("Publish Date").DataBodyRange(i).Value = "" And _
.ListColumns("Time to 1st Publish POC Status").DataBodyRange(i).Value = "No First POC" Then
.ListColumns("Call Down Start Date_CUSTOM").DataBodyRange(i).Value = .ListColumns("Activation Date").DataBodyRange(i).Value
Else
.ListColumns("Call Down Start Date_CUSTOM").DataBodyRange(i).Value = .ListColumns("Call Down Start Date").DataBodyRange(i).Value
End If
Next i

End With
End Sub

Answer 1

set lc = ActiveSheet.ListObjects(1).ListColumns
set range1 = lc("Activation Status").DataBodyRange
set range2 = lc("Call Down Start Date").DataBodyRange
set range3 = lc("Call Down Start Date_CUSTOM").DataBodyRange
set range4 = lc("Date Added").DataBodyRange
on error goto CATCH
Application.ScreenUpdating = False
With ActiveSheet.ListObjects(1)
For i = 1 To .DataBodyRange.Rows.Count
if range1(i).Value = "No" And _
range2(i).Value = "" then
range3(i).Value = range4(i).Value + 3
elseif
...
else
...
end if
next i
end with
CATCH: Application.ScreenUpdating = True
end sub