vba - MsgBox 后出现类型不匹配错误

Question

我的数据如下。

更新问题

Sub Solution()
  Dim shData As Worksheet
  Set shData = Sheets("Sheet1")    'or other reference to data sheet
  Dim coll As Collection, r As Range, j As Long
  Dim myArr As Variant
  Dim shNew As Worksheet

  shData.Activate

  'get unique values based on Excel features
  Range("a1").AutoFilter

  Set coll = New Collection

  On Error Resume Next

  For Each r In Range("A1:A10")
    coll.Add r.Value, r.Value
  Next r

  On Error GoTo 0
  'Debug.Print coll.Count

  For j = 1 To coll.Count
    MsgBox coll(j)
    myArr = coll(j)
  Next j

  Range("a1").AutoFilter

  Dim i As Long

  For i = 0 To UBound(myArr)
    shData.Range("$A$1").AutoFilter Field:=1, Criteria1:=myArr(i), _
      Operator:=xlAnd
    On Error Resume Next
    Sheets(myArr(i)).Range("A1").CurrentRegion.ClearContents

    If Err.Number = 0 Then
      Range("A1").CurrentRegion.Copy Sheets(myArr(i)).Range("A1")
    Else
      Set shNew = Sheets.Add(After:=Sheets(Sheets.Count))
      shData.Range("A1").CurrentRegion.Copy shNew.Range("A1")
      shNew.Name = myArr(i)
      Err.Clear
    End If
 Next i

 'removing filter in master sheet
 shData.Range("a1").AutoFilter

 End Sub

当我运行上面的宏时，我不知道为什么它在 MsgBox coll(j) 之后给出 Type Mismatch Error ，只是我想将数据存储在数组中，我正在传递该数据，这里我使用 For Each r In Range("A1:A10") 其中 A10 长度是静态的，如何找到最后写入的列？

Answer 1

当您向集合添加内容时，键需要是字符串，因此请使用:

coll.Add r.Value, CStr(r.Value)

而不是:

coll.Add r.Value, r.Value

您仍在将 coll(j) 分配给 Variant ，它不是数组。您需要:

ReDim myArr(1 to coll.Count)

在 for 循环之前，然后在循环中:

myArr(j) = coll(j)