Why Left function returns a run-time error '424'?

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

and the next line: `Sh.Cells(i, 5).PasteSpecial Paste:=xlPasteValues` remove it also. — Scott Craner, Feb 04 '19 at 19:38
@Scott Craner Thanks for noticing, I've edited. If I remove `.copy` and `.PasteSpecial`, how my code should meet its objective: overwriting cells with the first characters? — Drzemlik, Feb 04 '19 at 19:48
@Scott Craner, Now I see it works. copying/pasting not needed here:) Thank you! — Drzemlik, Feb 04 '19 at 19:53

score 5 · Accepted Answer · answered Feb 04 '19 at 19:48

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.

Accepted as the most valuable answer for me. Thank you for this info and your time! — Drzemlik, Feb 04 '19 at 20:16
@Drzemlik always a pleasure! Note that the `Left$` function returns (and intakesl an actual `String` and should be preferred over `Left`, which returns (and intakes) a `Variant` - see *Object Browser* (F2) for more like these; you'll find them under the `VBA.Strings` module. Cheers! — Mathieu Guindon, Feb 04 '19 at 20:20

score 1 · Answer 2 · answered Feb 04 '19 at 19:48

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

You are currently mistaken about the missing `End If` -- it is there in OP's code (it was put in by an OP-edit a few minutes before you posted this answer). — John Coleman, Feb 04 '19 at 20:06

Why Left function returns a run-time error '424'?

2 Answers2

Linked