变体数组多个错误和故障

Question

互联网的人，我需要你的帮助！ 我正在尝试使用变体数组将大型性能数据数据集汇总为单个分数。

我有一个大约 13000 行和大约 1500 名员工的桌子。

我对 VBA 并不陌生，以前使用过这种方法，所以我不知道出了什么问题。

当 for 循环超过数组的 UBound 时，我要么得到一个"下标超出范围"，要么得到一堆"下一个没有 for"、"结束选择而不选择"，无论"结束"或"下一个"是否存在。

请帮忙？

Sub createScore()
Dim loData As ListObject
Dim arrData() As Variant, arrSummary As Variant
Dim lRowCount As Long, a As Long, b As Long
  Set loData = Sheets("DataMeasure").ListObjects("tbl_g2Measure")
    arrData = loData.DataBodyRange
    lRowCount = Range("A6").Value
    Range("A8").Select
    For a = 1 To lRowCount
      Selection.Offset(1, 0).Select
        For b = LBound(arrData) To UBound(arrData)
          If arrData(b, 2) = Selection Then
            Select Case arrData(b, 8)
               Case "HIT"
                Selection.Offset(0, 3) = Selection.Offset(0, 3) + 1
            End Select
          End If
        Next b
    Next a
    Range("A8").Select
End Sub

Answer 1

不使用 Select 的快速重写。不过，这仍然没有从阵列中获得任何收益。

Sub createScore()
    Dim loData As ListObject
    Dim arrData() As Variant, arrSummary As Variant
    Dim lRowCount As Long, a As Long, b As Long
    Set loData = Sheets("DataMeasure").ListObjects("tbl_g2Measure")
    arrData = loData.DataBodyRange
    lRowCount = Range("A6").Value
    ' Update with correct sheet reference
    With ActiveSheet.Range("A8")
        For a = 1 To lRowCount
            For b = LBound(arrData, 1) To UBound(arrData, 1)
                If arrData(b, 2) = .Offset(a, 0).Value2 And arrData(b, 8) = "HIT" Then
                    .Offset(a, 3) = .Offset(a, 4)
                End If
            Next b
        Next a
    End With
End Sub

Answer 2

我需要在用户列表有重复的地方做类似的事情，所以我创建了一个唯一用户名数组：

Dim arr() As String
lrn = 13237 'ActiveSheet.Range("A1").Range("A1").SpecialCells(xlCellTypeLastCell).Row
ac = 0
ReDim arr(0 To ac) As String
For Each c In Range("L2:L" & lrn)
    If Not IsEmpty(c.Value) Then
        If Not (UBound(Filter(arr, c.Value)) > -1) Then
            If ac > 0 Then ReDim Preserve arr(0 To ac)
            arr(ac) = c.Value
            ac = ac + 1
        End If
    End If
    DoEvents
Next c