excel - 根据年份和唯一代码对多个数组的值进行排序

Question

我有一个包含大量数据的 excel 文档。我使用了四列。

1. 财政年度
2. 无效对冲的收益/损失
3. SIC 代码。
4. 已排序的唯一 SIC 代码，因此不会出现多次。

我创建了 4 个数组并将值存储在其中。

我想做的，但现在有一些问题，是每年（从 2001 年到 2018 年）并计算每个 SIC 代码的平均收益/损失（2），每年。我写了以下代码：

Sub Assignment4()

Dim Wb As Workbook
Dim Ws As Worksheet
Set Wb = ThisWorkbook
Set Ws = ThisWorkbook.Worksheets("Sheet1")

Dim iLastRow As Integer, iLastRow2 As Integer
Dim readStart As Integer
Dim count As Integer
Dim ArrayFiscalYear() As String
Dim ArrayGL() As String
Dim ArraySIC() As Variant
Dim ArraySICSorted() As Integer
ReDim ArrayFiscalYear(10000)
ReDim ArrayGL(10000)
ReDim ArraySIC(10000)
ReDim ArraySICSorted(10000)
Dim year As Integer
Dim arr As New Collection, a
Dim j As Long
Dim x As Long
Dim avg As Double
Dim sum As Double
Dim counter As Integer



iLastRow = Ws.Cells(Rows.count, "J").End(xlUp).Row
count = 2
readStart = 2



Do While (count <= iLastRow)
    ArraySIC(count) = Ws.Cells(count, 10)
    count = count + 1
Loop



    
Ws.Range("J2:J" & iLastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Ws.Range("L1"), _
Unique:=True

Ws.Cells(1, 12) = "Sorted SIC"


iLastRow2 = Ws.Cells(Rows.count, "L").End(xlUp).Row
Debug.Print (iLastRow2)

count = 0
counter = 2

Do While (count <= iLastRow2)
    ArraySICSorted(count) = Ws.Cells(counter, 12)
    Debug.Print (ArraySICSorted(count))
    count = count + 1
    counter = counter + 1
Loop


Ws.Columns(12).EntireColumn.Delete


i = 2
year = 2001
count = 0
x = 1
Do While year <= 2018
    For x = LBound(ArraySICSorted) To UBound(ArraySICSorted)
        For i = 1 To iLastRow
            If Ws.Cells(i, 3) = year Then
                ArrayFiscalYear(year) = Ws.Cells(i, 3)
                ArrayGL(year) = Ws.Cells(i, 8)
                ArraySIC(year) = Ws.Cells(i, 10)
                count = count + 1
                sum = sum + ArrayGL(year)
            End If
        Next i
        avg = sum / count
        Debug.Print (count & " --- " & ArraySICSorted(x) & " --- " & year & " --- " & x)
        sum = 0
        count = 0
        If x >= 62 Then
        year = year + 1
            Exit For
        End If
    Next x
    
Loop

End Sub

我附上了我的 excel 文档的屏幕截图

我希望有人能告诉我我做错了什么，以便我可以从中吸取教训。

标签： excelvba