VBA "Invalid Procedure Call or Argument" Error when extracting text before comma-CodePudding

I am receiving "Error 5" when trying to execute below code. Aim is to extract all characters before comma in a cell and then paste them to another cell based on the if statement. I am sure the last line causes the problem but I am not sure why.

Dim r As Long, commapos As Long, m As Long

m = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row


For r = 2 To m

        If Cells(r, 16).Value <> "0" And Cells(r, 9).Value = "" Then
        commapos = InStr(1, Cells(r, 8), ",")
        Cells(r, 9).Value = Left(Cells(r, 8), commapos - 1)
    
    End If
    
Next r
      End Sub

CodePudding user response：

On the Left function Syntax

Left(string, length) where length can't be less than 0

Try this:

Dim r As Long, commapos As Long, m As Long

m = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


For r = 2 To m

        If Cells(r, 16).Value <> "0" And Cells(r, 9).Value = "" Then
        commapos = InStr(1, Cells(r, 8), ",")
        
        If commapos <> 0 Then
            Cells(r, 9).Value = Left(Cells(r, 8), commapos - 1)
        End If
        
        
    End If
    
Next r