Why Left function returns a run-time error '42

Question

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

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

Answer 1

You need to understand how methods and assignment operations work.

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

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)

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.

So you can't do this:

Debug.Print "ABC".Copy

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

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

---

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

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.

Answer 2

I saw some mistakes in the code, look:

• If Dir(SourceFolder & "Filename.*") <> "" Then: Doesn't have a End If at the end of the code.

• Sh.Cells(i, 5) = Left(Sh.Cells(i, 5).Value, 10).Copy: You doesn't need the .copy at the end, you're already setting the value.

At the end you should have a code like this:

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)
                Sh.Cells(i,5).Interior.ColorIndex = 6
            End If
        Next i
    End If
End sub