excel - VBA 集合 - 将值从静态范围读入集合

Question

我正在尝试将一系列值放入一个新集合中。范围是固定的，应该跳过空单元格，将内容添加到集合中的单元格。但是，我收到一条错误消息

"Object variable or With block variable not set"

在线上

"ISINsLX0358 = ThisWorkbook.Sheets("Splits_Vormonat").Range("B3:BK3")"

有人可以告诉我为什么会这样并帮助我改进我的代码吗？

Dim collLX0358 As New Collection
Dim ISINsLX0358 As Range

Set collLX0358 = Nothing

ISINsLX0358 = ThisWorkbook.Sheets("Splits_Vormonat").Range("B3:BK3")

For Each isin In ISINsLX0358
    If isin <> "" Then
        coll.Add isin
    End If
Next isin

Answer 1

瞧:

Option Explicit

Public Sub TestMe()

    'Dim collLX0358  As New Collection - you are not using it, -> you do not need it.
    Dim ISINsLX0358 As Range

    Dim isin        As Range
    Dim coll        As New Collection
    'Set collLX0358 = Nothing - WHY?

    Set ISINsLX0358 = ThisWorkbook.Sheets(1).Range("B3:BK3")

    For Each isin In ISINsLX0358
        If isin <> "" Then
            coll.Add isin
        End If
    Next isin

End Sub

开始使用Option Explicit

Set colLX0358 = Nothing 的想法是什么.只要您不使用它，请将其从代码中删除。

Range是 VBA 中的一个对象，它应该与单词 Set 一起使用