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

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

最佳答案

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

为什么要数组？因为myCell.Value是一个数组。在这种情况下，myCell引用整个单元格列。高温超导

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