代码之家 › 专栏 › 技术社区 › Error 1004

试图从表中获取唯一值时出现“赋值到常量”错误

unique compiler-errors excel-vba vba excel

Error 1004 · 技术社区 · 7 年前

tblDetails 柱 Names tblNames 列名。

但是在下面代码的最后一行:

tbl2.Resize(d.Count) = Application.Transpose(d.keys)

…我收到这个错误:

编译错误:

任何帮助都将不胜感激。

Sub Get_Unique_Values()
    Dim dict As Object, arr, j, arrCustomers
    Set dict = CreateObject("Scripting.Dictionary")
    Dim tbl1 As ListObject, tbl2 As ListObject
    Dim d As Object, i As Long, c As Variant

    Set tbl1 = Worksheets("Sheet1").ListObjects("tblTest")
    Set tbl2 = Worksheets("Sheet1").ListObjects("tblTest2")

    If Not tbl2.DataBodyRange Is Nothing Then 'Clean tblTest2
        tbl2.AutoFilter.ShowAllData
        tbl2.DataBodyRange.Delete
    End If

    Set d = CreateObject("Scripting.Dictionary")
    c = tbl1.ListColumns(1).DataBodyRange 'Loop through Table
    For i = 1 To UBound(c, 1)
        d(c(i, 1)) = 1
    Next i

    tbl2.Resize(d.Count) = Application.Transpose(d.keys) 'Export result to table
End Sub

1 回复 | 直到 7 年前

Error 1004 7 年前

@约翰@ashleedawg@骚扰了爸爸,在我多次试图得到我想要的东西之后,感谢大家的回应和指导。

Sub Get_Unique_Values()

Dim i As Long
Dim tbl1 As ListObject, tbl2 As ListObject
Dim d As Object
Dim c As Variant
Dim CountD As Long, LastRow As Long
Dim rng As Range

Set tbl1 = Worksheets("Sheet1").ListObjects("tblNames")
Set tbl2 = Worksheets("Sheet1").ListObjects("tblTest2")

If Not tbl2.DataBodyRange Is Nothing Then 'Clean tblTest2
    tbl2.AutoFilter.ShowAllData
    tbl2.DataBodyRange.Delete
End If

Set d = CreateObject("Scripting.Dictionary")
c = tbl1.ListColumns(1).DataBodyRange 'Loop through Table
For i = 1 To UBound(c, 1)
    d(c(i, 1)) = 1
Next i

CountD = d.Count
LastRow = Sheet1.ListObjects("tblTest2").Range.Rows.Count

Set rng = Range("tblTest2[#All]").Resize(tbl2.Range.Rows.Count + CountD - 1, tbl2.Range.Columns.Count)

tbl2.Resize rng

Range("tblTest2[Column1]") = Application.Transpose(d.keys)  'Export result to table

End Sub

推荐文章