Need help with Error: run time error 5 invalid procedure call or argument when code is executed

[Zee996]

Hello, I am new to VBA.
I need help understanding why this code is throwing an error.
VBA Code: Use to delete the characters from a string.
It is working fine in the original sheet but throwing errors in another sheet.

Sub Delete_Left_Text()
Worksheets("sheet2").Activate
Dim c As Range
With Range("B1")
    For Each c In Range("B2", .Offset(.CurrentRegion.Rows.Count - 1))
    c.Value = Right(c.Value, Len(c.Value) - 27)[/COLOR]                         <<<Error on this line
    Next c
End With
End Sub

Please help me with this.
Thank you!

 

[frabulator]

Hello, I am new to VBA.
I need help understanding why this code is throwing an error.
VBA Code: Use to delete the characters from a string.
It is working fine in the original sheet but throwing errors in another sheet.

Sub Delete_Left_Text()
Worksheets("sheet2").Activate
Dim c As Range
With Range("B1")
    For Each c In Range("B2", .Offset(.CurrentRegion.Rows.Count - 1))
    c.Value = Right(c.Value, Len(c.Value) - 27)[/COLOR]                         <<<Error on this line
    Next c
End With
End Sub

Please help me with this.
Thank you!

That error is happening because you are subtracting more than the cell has in it.
Please see below:

Sub Delete_Left_Text()
    'Worksheets("sheet2").Activate
    Dim c As Range
    With Range("B1")
        For Each c In Range("B2", .Offset(.CurrentRegion.Rows.Count - 1))
            Dim t As String
            t = c.Value                             'value of "2"
            t = Right(c.Value, Len(c.Value))        'returns "2"
            t = Right(c.Value, Len(c.Value) - 1)    'returns ""
            t = Right(c.Value, Len(c.Value) - 2)    'Errors out
            
        'c.Value = Right(c.Value,)
        Next c
    End With
End Sub

 

[Zee996]

That error is happening because you are subtracting more than the cell has in it.
Please see below:

Sub Delete_Left_Text()
    'Worksheets("sheet2").Activate
    Dim c As Range
    With Range("B1")
        For Each c In Range("B2", .Offset(.CurrentRegion.Rows.Count - 1))
            Dim t As String
            t = c.Value                             'value of "2"
            t = Right(c.Value, Len(c.Value))        'returns "2"
            t = Right(c.Value, Len(c.Value) - 1)    'returns ""
            t = Right(c.Value, Len(c.Value) - 2)    'Errors out
           
        'c.Value = Right(c.Value,)
        Next c
    End With
End Sub

Thank you!
But the cell has 30 characters in it and I like to remove the first 27 of it.

 

[Zee996]

Error is only showing when I minimized the main workbook with VBA code and ran the macro on other workbooks.

 

[frabulator]

Try running this and see what it says.

Sub Delete_Left_Text()
    'Worksheets("sheet2").Activate
    Dim c As Range
    With Range("B1")
        For Each c In Range("B2", .Offset(.CurrentRegion.Rows.Count - 1))
            Dim t As String
            t = c.Value
            Dim l As Integer
            l = Len(t)
            If l < 3 Then l = 3
            t = Mid(t, l - 2, 3)
            Dim result As Variant
            result = MsgBox("Cell is in: " & CStr(c.Address) & " with a value of " & c.value & " | with the last three characters being " & t, vbQuestion + vbYesNo + vbDefaultButton2, "cell value.")
            If result = vbNo Then Exit For
        Next
    End With
End Sub

--EDIT--
I edited this because i saw you needed the last three and not the first three
--EDIT--

The above should give you the LAST three characters in that cell regardless of how many characters there are. It will give you message box telling you the location and cell value it is pulling. This should give you a good idea where it is pulling from and what values.

 

[Peter_SSs]

I need help understanding why this code is throwing an error.

Almost certainly because the cell being processed is not the one that you think is being processed and it is a character length issue. There is nothing in your code to tell it to operate in the "other workbook"

As a test, first put the test macro below in the main workbook where your original macro is housed then set up the workbooks as described below and run the test code.

Error is only showing when I minimized the main workbook with VBA code and ran the macro on other workbooks.

Sub Test()
  Worksheets("sheet2").Activate
  Dim c As Range
  With Range("B1")
    For Each c In Range("B2", .Offset(.CurrentRegion.Rows.Count - 1))
    MsgBox "c.Value: " & c.Value & vbLf & "Len(c.Value): " & Len(c.Value) & vbLf & _
      "Worksheet: " & c.Parent.Name & vbLf & "Workbook: " & c.Parent.Parent.Name
    Exit Sub
    Next c
  End With
End Sub

Does that message box contain anything unexpected?

A couple of other things.

• If you want to highlight something in the code you post in the forum, use the 'RICH' code tags, not the VBA tags.
  
  
• Her is another approach to your original problem to do all the cells at once (in 'sheet2' of the main workbook as a test)

Sub Delete_Left_Text_v2()
  With Worksheets("sheet2").Range("B1")
    .Offset(1).Resize(.CurrentRegion.Rows.Count - 1).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(27, 1))
  End With
End Sub