excel - 添加 ".Cells"如何解决运行时错误 13？

Question

您能解释一下 this post 的解决方案吗？ ？我遇到了同样的问题，但无法理解添加“.Cells”如何解决运行时错误 13“类型不匹配”的问题？

Answer 1

错误 13 是由传递给 Clean 的参数不匹配引起的。功能。此函数需要 string，但这里传递了 array。

为什么要数组？因为 myCell.Value 是一个数组。在本例中，myCell 指的是整列单元格。 HTH

Option Explicit

Sub test()
    Dim myCell As Variant
    Dim myValue As Variant

    For Each myCell In Columns("G:G")
        Debug.Print TypeName(myCell) ' Range
        Debug.Print myCell.Address ' $G:$G
        Debug.Print myCell.Cells.Count ' e.g. 1048576

        myValue = myCell.Value
        Debug.Print TypeName(myValue) ' Variant()
        Debug.Print IsArray(myValue) ' True

        ' Here comes the type mismatch from
        ' It is because Clean accepts not an array, but a string
        WorksheetFunction.Clean (myCell.Value)

        ' It fails beause of mismatch argument typy which is passed to Clean function
        myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value))
    Next
End Sub

因此，Columns("G:G") 返回一列中所有单元格的Range。代码可以写成例如像这样展示它。

Sub CellsOfColumn()
    Dim myColumn As Range
    Dim myCell As Range

    For Each myColumn In Columns("G:G")
        Debug.Print TypeName(myColumn) ' Range 
        Debug.Print myColumn.Address ' $G:$G
        Debug.Print myColumn.Cells.Count ' e.g. 1048576
        For Each myCell In myColumn.Cells
            Debug.Print TypeName(myCell) ' Range
            Debug.Print myCell.Address ' $G$1, $G$2, $G$3, $G$4 ...
            Debug.Print myCell.Cells.Count ' 1, 1, 1, 1, ...
            ' This now works because 
            ' myCell.Value is now a single value not an array,
            ' where myCell refers not to column, but to a single cell  
            myCell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(myCell.Value))
        Next
    Next
End Sub