时间戳

首页 > 解决方案 > 阵列仍然运行缓慢

arrays - 阵列仍然运行缓慢

问题描述

谁能告诉我为什么当我使用数组时下面的代码运行速度没有更快？似乎需要 10 分钟或更长时间才能运行，这很奇怪，因为昨天没有阵列可能需要一两分钟

Sub populateHRData9()

hrArray = Worksheets("HR_Report").Range("A1").CurrentRegion.Value
Set report = ActiveWorkbook.Worksheets("Report")


For x = 2 To report.UsedRange.Rows.Count
    If report.Cells(x, 1) = "" Then
    End If
Next x


For j = 2 To UBound(hrArray, 1)
    If hrArray(j, 42) <> "United Kingdom" Or hrArray(j, 42) <> "France" Or 
hrArray(j, 42) <> "Ireland" Or hrArray(j, 42) <> "Italy" Or hrArray(j, 42) <> 
"Netherlands" Or hrArray(j, 42) <> "Spain" Or hrArray(j, 42) <> "Sweden" Or 
hrArray(j, 42) <> "Germany" Or hrArray(j, 42) <> "Austria" Then
    If hrArray(j, 10) = "WEALTH MANAGEMENT" Or hrArray(j, 10) = "INTELLIGENT 
DIGITAL SOLUTIONS" Then
    If hrArray(j, 18) = "Administrative Asst - AM Investors/WM Solutions" Or 
hrArray(j, 18) = "Administrative Asst (Sales Service)" Or hrArray(j, 18) = 
"Administrative Asst (Sales/CA Support)" Or hrArray(j, 18) = "Client Service 
Total" Or hrArray(j, 18) = "Communications" Or hrArray(j, 18) = "Fiduciatary" 
Or hrArray(j, 18) = "Front Office Interns" Or hrArray(j, 18) = "Investments" 
Or hrArray(j, 18) = "Investors Service - ex Program Analysts" Or hrArray(j, 
18) = "JPMS Financial Advisors" Or hrArray(j, 18) = "JPMS Solutions" Or 
hrArray(j, 18) = "Marketing and Events" Or hrArray(j, 18) = "Mortgage 
Advisory" Or hrArray(j, 18) = "Origination/Client Manager" Or hrArray(j, 18) 
= "Other" Or hrArray(j, 18) = "Solutions - Program Analyst" Or hrArray(j, 18) 
= "Summer Intern" Or hrArray(j, 18) = "Supervisory Management" Or hrArray(j, 
18) = "WM Bankers" Or hrArray(j, 18) = "WM Capital Advisors" _
    Or hrArray(j, 18) = "WM Investors" Or hrArray(j, 18) = "WM MM/RH/PL" Or 
hrArray(j, 18) = "WM Prosectors" Or hrArray(j, 18) = "WM Trusts Officers" Or 
hrArray(j, 18) = "WM Wealth Advisors" Or hrArray(j, 18) = "WMOC" Then
    report.Cells(x, 1) = hrArray(j, 3)


 x = x + 1
    End If
    End If
    End If

Next j

End Sub

原谅多个 if 语句。当我使用或时，如果跳过“英国”。这是我之前的代码，实际上在重新启动计算机后它又快了。

Sub populateHRData9()


Set report = ActiveWorkbook.Worksheets("Report")
Set hr = ActiveWorkbook.Worksheets("HR_Report")


For x = 2 To report.UsedRange.Rows.Count
    If report.Cells(x, 1) = "" Then
    End If
Next x


For j = 2 To hr.UsedRange.Rows.Count
    If hr.Cells(j, 42) <> "United Kingdom" Then
    If hr.Cells(j, 42) <> "France" Then
    If hr.Cells(j, 42) <> "Ireland" Then
    If hr.Cells(j, 42) <> "Italy" Then
    If hr.Cells(j, 42) <> "Netherlands" Then
    If hr.Cells(j, 42) <> "Spain" Then
    If hr.Cells(j, 42) <> "Sweden" Then
    If hr.Cells(j, 42) <> "Germany" Then
    If hr.Cells(j, 42) <> "Austria" Then
    If hr.Cells(j, 10) = "WEALTH MANAGEMENT" Or hr.Cells(j, 10) = 
"INTELLIGENT DIGITAL SOLUTIONS" Then
    If hr.Cells(j, 18) = "Administrative Asst - AM Investors/WM Solutions" 
Or hr.Cells(j, 18) = "Administrative Asst (Sales Service)" Or hr.Cells(j, 
18) = "Administrative Asst (Sales/CA Support)" Or hr.Cells(j, 18) = "Client 
Service Total" Or hr.Cells(j, 18) = "Communications" Or hr.Cells(j, 18) = 
"Fiduciatary" Or hr.Cells(j, 18) = "Front Office Interns" Or hr.Cells(j, 18) 
= "Investments" Or hr.Cells(j, 18) = "Investors Service - ex Program 
Analysts" Or hr.Cells(j, 18) = "JPMS Financial Advisors" Or hr.Cells(j, 18) 
= "JPMS Solutions" Or hr.Cells(j, 18) = "Marketing and Events" Or 
hr.Cells(j, 18) = "Mortgage Advisory" Or hr.Cells(j, 18) = 
"Origination/Client Manager" Or hr.Cells(j, 18) = "Other" Or hr.Cells(j, 18) 
= "Solutions - Program Analyst" Or hr.Cells(j, 18) = "Summer Intern" Or 
hr.Cells(j, 18) = "Supervisory Management" Or hr.Cells(j, 18) = "WM Bankers" 
Or hr.Cells(j, 18) = "WM Capital Advisors" _
    Or hr.Cells(j, 18) = "WM Investors" Or hr.Cells(j, 18) = "WM MM/RH/PL" 
Or hr.Cells(j, 18) = "WM Prosectors" Or hr.Cells(j, 18) = "WM Trusts 
Officers" Or hr.Cells(j, 18) = "WM Wealth Advisors" Or hr.Cells(j, 18) = 
"WMOC" Then
    report.Cells(x, 1) = hr.Cells(j, 3)


 x = x + 1
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If

Next j

End Sub

标签： arraysexcelvba

解决方案

好的，所以当我想到这一点时，我意识到您实际上是在向后使用数组 - 我认为这不会让您更快地将这些初始值存储在数组中。我相信您真正想要成为数组的部分是您要检查的值，所以这就是我对它们所做的！

这是未经测试的，所以让我知道它是否有效/更快。

Sub populateHRData9()

    Dim hrArray() As Variant
    hrArray = Worksheets("HR_Report").Range("A1").CurrentRegion.value

    Dim report As Worksheet
    Set report = ActiveWorkbook.Worksheets("Report")

    Dim lastRow As Long
    lastRow = report.Cells(report.Rows.count, "A").End(xlUp).row


    pt1 = Array("United Kingdom", "France", "Ireland", "Italy", "Netherlands", "Spain", "Sweden", "Germany", "Austria")

    pt2 = Array("WEALTH MANAGEMENT", "INTELLIGENT DIGITAL SOLUTIONS")

    pt3 = Array("Administrative Asst - AM Investors/WM Solutions", "Administrative Asst (Sales Service)", "Administrative Asst (Sales/CA Support)", _
        "Client Service Total", "Communications", "Fiduciatary", "Front Office Interns", "Investments", "Investors Service - ex Program Analysts", _
        "JPMS Financial Advisors", "JPMS Solutions", "Marketing and Events", "Mortgage Advisory", "Origination/Client Manager", _
        "Other", "Solutions - Program Analyst", "Summer Intern", "Supervisory Management", "WM Bankers", "WM Capital Advisors", "WM Investors", _
        "WM MM/RH/PL", "WM Prosectors", "WM Trusts Officers", "WM Wealth Advisors", "WMOC")

    Dim j As Long
    For j = 2 To UBound(hrArray, 1)

        If Not IsInArray(hrArray(j, 42), pt1) _
        And IsInArray(hrArray(j, 10), pt2) _
        And IsInArray(hrArray(j, 18), pt3) Then
            report.Cells(lastRow, 1) = hrArray(j, 3)
            lastRow = lastRow + 1
        End If
    Next j

End Sub

Function IsInArray(strIn As String, arrList() As Variant) As Boolean
    IsInArray = Not (IsError(Application.Match(strIn, arrList, 0)))
End Function

推荐阅读