时间戳

首页 > 解决方案 > VBA - 从动态范围中获取唯一值

excel - VBA - 从动态范围中获取唯一值

问题描述

我使用了来自vba的 eksortso 的答案：从数组中获取唯一值以从数组中获取唯一值

Sub Trial()
    Dim myArray() As Variant
    Dim i As Long
    Dim d As Object

    myArray() = Array("Banana", "Apple", "Orange", "Tomato", "Apple", "Lemon", "Lime", "Lime", "Apple")

    Set d = CreateObject("Scripting.Dictionary")

    For i = LBound(myArray) To UBound(myArray)
        d(myArray(i)) = 1
    Next i

End Sub

这非常有效，但是当我尝试将其应用于从工作表中读取的范围时，它给了我一个错误 -Run-time error '9': Subscript out of range

Sub Clients()

    Dim Sht As Worksheet
    Dim LastRow As Long
    Dim StartCell As Range
    Dim ClientType As Variant
    Dim UniqueType As Object
    Dim i As Long

    Set Sht = Worksheets("ALL CLIENTS")
    Set StartCell = Range("F6")

    'Find Last Row
    LastRow = Sht.Cells(Sht.Rows.Count, StartCell.Column).End(xlUp).Row

    'Read Client Type Column
    ClientType = Sht.Range(StartCell, Sht.Cells(LastRow, StartCell.Column))

    Set UniqueType = CreateObject("Scripting.Dictionary")

    For i = (LBound(ClientType) - 1) To UBound(ClientType)
        UniqueType(ClientType(i)) = 1
    Next i

End Sub

发生这种情况是因为myArray从下标开始0而ClientType从 开始1？我该如何解决？

标签： excelvba

解决方案

是ClientType的将以 1 为基础。

删除它-1，并记住您正在使用 2D 数组：

For i = LBound(ClientType, 1) To UBound(ClientType, 1)
    UniqueType(ClientType(i, 1)) = 1
Next i

当列表中只有一个单元格时可能出现故障模式，因为在这种情况下，您将无法获得二维数组ClientType

推荐阅读