为什么Left函数返回运行时错误'424'? [英] Why Left function returns a run-time error '424'?

问题描述

下面的代码应从一个单元格中复制值，并将其前10个字符粘贴到该范围内的同一单元格中.在这一行:

The code below should copy values from a cell and paste its first 10 characters to the same cell in the range. On this line:

Sh.Cells(i, 5) = Left(Sh.Cells(i, 5).Value, 10).Copy

我收到运行时错误"424"(需要对象).在该行之前添加"set"不起作用.有谁知道为什么在这里触发错误?

I get a run-time error '424' (object required). Adding "set" before the line does not work. Does anyone know why is the error triggered here?

Sub fixCellsValue()
    Dim wrk As Workbook
    Dim Sh As Worksheet
    Dim SourceFolder As String
    Dim i As Long, lastrow As Long

    SourceFolder = ThisWorkbook.PATH & "\source"

    If Dir(SourceFolder & "Filename.*") <> "" Then

        Set wrk = Application.Workbooks.Open(SourceFolder & "\Filename.xlsx")
        Set Sh = wrk.Worksheets(1)

        lastrow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).row

        For i = 2 To lastrow
            If Len(Sh.Cells(i, 5)) > 10 Then
                Sh.Cells(i, 5) = Left(Sh.Cells(i, 5).Value, 10).Copy
                Sh.Cells(i, 5).PasteSpecial Paste:=xlPasteValues
                Sh.Cells(i,5).Interior.ColorIndex = 6
            End If
        Next i
    End If
End sub

推荐答案

您需要了解方法和赋值操作的工作原理.

You need to understand how methods and assignment operations work.

Sh.Cells(i, 5) = Left(Sh.Cells(i, 5).Value, 10).Copy

这是为左侧(LHS)表达式Sh.Cells(i, 5).Value(通过隐式默认成员调用)分配右侧(RHS)表达式返回的值-但是RHS不返回任何内容. /p>

This is assigning the left-hand side (LHS) expression Sh.Cells(i, 5).Value (through an implicit default member call) the value returned by the right-hand side (RHS) expression - however the RHS isn't returning anything.

Left(Sh.Cells(i, 5).Value, 10)

此表达式返回一个最长10个字符的Variant/String.在VBA中，String只是一个值(类似于Integer或Long是，但是它包含文本)，并且VBA中的值没有成员方法.

This expression returns a Variant/String that is up to 10 characters long. In VBA, a String is just a value (like an Integer or Long is, except it contains text), and values in VBA don't have member methods.

所以你不能这样做:

Debug.Print "ABC".Copy

因为成员调用需要一个对象-因此，需要对象.

Because a member call requires an object - hence, object required.

删除.Copy成员调用，即可解决此错误.

Drop the .Copy member call, you'll fix this error.

Sh.Cells(i, 5).PasteSpecial Paste:=xlPasteValues

从技术上讲是多余的-通过直接分配单元格的Value来完成该操作之前的那一行.但是，如果要调用Range.Copy，则不能将其作为RHS表达式的一部分使用，因为Range.Copy不返回任何内容-因此您将执行以下操作:

That's technically redundant - the line before it has just done exactly that, by assigning the cell's Value directly. But if you want to invoke Range.Copy, you can't do it as part of a RHS expression, because Range.Copy doesn't return anything - so you would do something like this:

Sh.Cells(i, 5).Copy
Sh.Cells(i, 5).PasteSpecial Paste:=xlPasteValues

但是再说一次，这是多余的-您不需要在这里使用剪贴板.

But then again, that's redundant - you don't need to involve the clipboard here.

这篇关于为什么Left函数返回运行时错误'424'?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！