vba - 对于 70,000 行,我的宏需要大量时间来根据各种条件给出输出注释
问题描述
为了遍历 70,000 行(使用 for 循环),我的宏需要大量时间来根据各种条件给出输出注释。我正在寻求优化这一点。
请查看描述数据外观的图像。
此代码比较各个列并根据某些条件给出输出。
Set wsa = ThisWorkbook.Sheets("Audit-NIMS vs Site Topology")
With wsa
AudLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Cells(1, AudLastCol + 1).Value = "Match;Issue Type;Actions"
For l = 2 To AudLastRow
aPRTS = .Cells(l, AudLastCol).Value
bNIMS = .Cells(l, NIMsLastCol).Value
tempin = .Cells(l, 2).Value
If aPRTS = bNIMS Then
Deployed19 = Application.Match("Deployed(1.9)", .Rows(1), 0)
If IsNumeric(Deployed19) Then
d19 = .Cells(l, Deployed19).Value
Else
d19 = 0
End If
Deployed800 = Application.Match("Deployed (800)", .Rows(1), 0)
If IsNumeric(Deployed800) Then
d8 = .Cells(l, Deployed800).Value
Else
d8 = 0
End If
Deployed2500 = Application.Match("Deployed (2.5)", .Rows(1), 0)
If IsNumeric(Deployed2500) Then
d25 = .Cells(l, Deployed2500).Value
Else
d25 = 0
End If
PRTS800 = Application.Match("Total-800-PRTS", .Rows(1), 0)
If IsNumeric(PRTS800) Then
p8 = .Cells(l, PRTS800).Value
Else
p8 = 0
End If
PRTS1900 = Application.Match("Total-1900-PRTS", .Rows(1), 0)
If IsNumeric(PRTS1900) Then
p19 = .Cells(l, PRTS1900).Value
Else
p19 = 0
End If
PRTS2500 = Application.Match("Total-2500-PRTS", .Rows(1), 0)
If IsNumeric(PRTS2500) Then
p25 = .Cells(l, PRTS2500).Value
Else
p25 = 0
End If
If (p19 = d19) And (p8 = d8) And (p25 = d25) Then
.Cells(l, AudLastCol + 1).Value = "TRUE;None;No Action Required."
Else
.Cells(l, AudLastCol + 1).Value = "FALSE;Both;Update NIMS and PRTS."
End If
ElseIf aPRTS = "NA" And bNIMS = "0" Then
.Cells(l, AudLastCol + 1).Value = "TRUE;None;No Action Required."
ElseIf aPRTS = "0" And bNIMS = "NA" Then
.Cells(l, AudLastCol + 1).Value = "TRUE;None;No Action Required."
ElseIf aPRTS > 0 And bNIMS = "NA" Then
.Cells(l, AudLastCol + 1).Value = "N/A;NIMS;Update NIMS."
ElseIf bNIMS > 0 And aPRTS = "NA" Then
.Cells(l, AudLastCol + 1).Value = "N/A;PRTS;Check traffic from PRTS & Report to PRTS Team."
ElseIf bNIMS > aPRTS Then
.Cells(l, AudLastCol + 1).Value = "FALSE;PRTS;Check traffic from PRTS & Report to PRTS Team."
ElseIf bNIMS < aPRTS Then
.Cells(l, AudLastCol + 1).Value = "FALSE;NIMS;Update NIMS."
End If
'To compare certain category of Name and concatenate output accordingly.
If InStr(1, .Cells(l, 1).Value, "52XC") > 0 Then
.Cells(l, AudLastCol + 1).Value = .Cells(l, AudLastCol + 1).Value & "Clearwire Site."
ElseIf InStr(1, .Cells(l, 1).Value, "82XC") > 0 Then
.Cells(l, AudLastCol + 1).Value = .Cells(l, AudLastCol + 1).Value & "Clearwire Site."
ElseIf InStr(1, .Cells(l, 1).Value, "XT") > 0 Then
.Cells(l, AudLastCol + 1).Value = .Cells(l, AudLastCol + 1).Value & "COW Site."
End If
If bNIMS = "NA" And Application.CountIf(ThisWorkbook.Sheets("NIMS dump-SC level").Range("B1:B" & temprow), .Cells(l, 2).Value) Then
.Cells(l, AudLastCol + 1).Value = Cells(l, AudLastCol + 1).Value & "Present in NIMS Dump."
End If
Next l
End With
解决方案
推荐阅读
- json - powershell 以 JSON 格式编辑 localstate 文件
- opengl - GLSL,手动深度测试
- python - dask 存储库中的语义版本控制
- r - rmarkdown 纵向 pdf 中的宽 huxtable 表,页面旋转为横向
- jquery - 无法使用从我的数据库中检索到的行来构建
- 项目
- parameters - 动态滑块 Tableau 百万/十亿
- c - 在汇编中,我如何从调用函数中获取值?
- python - 如何测试装饰器是否在装饰方法或函数?
- java - 通过 Spring Data 存储库在内部保存带有大 Map 的 POJO 导致 StackOverflowError
- xcode - XCode 11 和 Cmake 语法高亮