excel - method range of object '_Global' failed trying to modify text - Stack Overflow

I have tried several different ways of removing the first 5 characters of text that is in column I and displaying it in column H, but I keep getting the range of object error:

Sub TRIM_SSP_CORE()
           
    With Sheet8.Range("A:B,E:J,L:AK,AN:AQ,AS:AW,AY:BF,BH:BH,BJ:BM,BO:BO")
        .Columns.Delete shift:=xlToLeft
    End With
    
    With Sheet8
        .Columns("I:I").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("H:H").Hidden = True
    End With
    
    With Sheet8
        .Range("I2:I").value = Left(Range("H2:H").value, -5)
    End With

End Sub

I have even tried this:

Sub TRIM_SSP_CORE()

Dim i As Integer
Dim TN As String

    With Sheet8.Range("A:B,E:J,L:AK,AN:AQ,AS:AW,AY:BF,BH:BH,BJ:BM,BO:BO")
        .Columns.Delete shift:=xlToLeft
    End With

    With Sheet8
        .Columns("I:I").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("H:H").Hidden = True
    End With

    With Sheet8
        For i = 2 To 2000
            TN = Range("I" & i)
            Range("H" $ i) = Left(TN, Len(TN) - 5)
        Next i
    End With

End Sub

Any guidance would be appreciated.

I have tried several different ways of removing the first 5 characters of text that is in column I and displaying it in column H, but I keep getting the range of object error:

Sub TRIM_SSP_CORE()
           
    With Sheet8.Range("A:B,E:J,L:AK,AN:AQ,AS:AW,AY:BF,BH:BH,BJ:BM,BO:BO")
        .Columns.Delete shift:=xlToLeft
    End With
    
    With Sheet8
        .Columns("I:I").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("H:H").Hidden = True
    End With
    
    With Sheet8
        .Range("I2:I").value = Left(Range("H2:H").value, -5)
    End With

End Sub

I have even tried this:

Sub TRIM_SSP_CORE()

Dim i As Integer
Dim TN As String

    With Sheet8.Range("A:B,E:J,L:AK,AN:AQ,AS:AW,AY:BF,BH:BH,BJ:BM,BO:BO")
        .Columns.Delete shift:=xlToLeft
    End With

    With Sheet8
        .Columns("I:I").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Columns("H:H").Hidden = True
    End With

    With Sheet8
        For i = 2 To 2000
            TN = Range("I" & i)
            Range("H" $ i) = Left(TN, Len(TN) - 5)
        Next i
    End With

End Sub

Any guidance would be appreciated.

• excel
• vba

Share Improve this question Follow asked 14 hours ago Iron ManIron Man 79511 gold badge1212 silver badges2828 bronze badges 4

• 3 Note that in yout "I have even tried" code you haven't qualified Range(...). Use .Range(...) – chris neilsen Commented 13 hours ago
• Also, I am pretty sure ("H" $ i) should be ("H" & i) – braX Commented 7 hours ago
• text that is in column I and displaying it in column H ?? Column I is a new blank column and Column H is hidden, do you mean display in col I the col H values like your first code .Range("I2:I").value = Left(Range("H2:H").value, -5) – CDP1802 Commented 5 hours ago
• @braX - Yeah, typo when prepping to copy it here. Good catch. – Iron Man Commented 1 hour ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

There are at least two issues with your code:

• Negative length in left function: The Left function syntax is Left(String, Length), where Length must be a positive integer. Using -5 as the length is invalid and will cause an error.
• Range handling: When you reference Range("H2:H"), you're selecting an entire column from row 2 downwards. Assigning Left(Range("H2:H").Value, 5) directly to another range doesn't work because Left expects a single string, not an array of values from multiple cells.

You can try something like:

With Sheet8

        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
        
        ' Trim the first 5 characters from column H and place in column I
        For Each cell In .Range("H2:H" & lastRow)
            If Len(cell.Value) >= 5 Then
                cell.Offset(0, 1).Value = Mid(cell.Value, 6) ' Remove first 5 characters
            Else
                cell.Offset(0, 1).Value = "" ' Handle cases where text length is less than 5
            End If
        Next cell
End With