VBA Trim error

[LuciakPL]

Hei.

I've been trying different things but I can't find what the issue is.

When I use code below as is, I get run-time error -2147417848 (80010108).
When I remove line 5 (the one with Trim function), it works ok.
I test it manually adding spaces in from and after the text value in a cell.

If (Target.Column = 2) Then    Application.ScreenUpdating = False
    lastRowB = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row
        For i = 2 To lastRowB
        ActiveSheet.Range("B" & i) = Trim(ActiveSheet.Range("B" & i))
        If (Len(ActiveSheet.Range("B" & i)) = Len(Replace(ActiveSheet.Range("B" & i), " ", ""))) Then
            ActiveSheet.Range("C" & i) = ActiveSheet.Range("B" & i)
        Else
            ActiveSheet.Range("C" & i) = Replace(ActiveSheet.Range("B" & i), _
                String(Len(ActiveSheet.Range("B" & i)) - Len(Replace(ActiveSheet.Range("B" & i), " ", "")), " "), _
                String(9 - (InStr(ActiveSheet.Range("B" & i), " ") - 1), " "))
        End If
    Next i
    Application.ScreenUpdating = True
End If

 

[RoryA]

Welcome to the forum.

Does it make any difference if you use this instead:

ActiveSheet.Range("B" & i).Value = VBA.Trim(ActiveSheet.Range("B" & i).Value)

 

[Dr. Demento]

Welcome!!!

I believe your code is trying to TRIM a range instead of the value within the Range. Try this:

 ActiveSheet.Range("B" & i) = Trim(ActiveSheet.Range("B" & i).Value)

 

[Joe4]

A few things:

1. You do not need your "ActiveSheet." references. If you leave those off, it assumes the "ActiveSheet". Also, by your use of Target, I am guessing this part of a Worksheet event procedure code. Those, by definition, run on the ActiveSheet.

2. If this is part of a Worksheet_Change event procedure, you need to disable events while updating column B, or you will get caught in an endless loop!

This code worked for me:

If Target.Column = 2 Then
    Application.ScreenUpdating = False
    lastRowB = Range("B" & Rows.Count).End(xlUp).Row
    Application.EnableEvents = False
    For i = 2 To lastRowB
        Range("B" & i) = Trim(Range("B" & i))
        If (Len(Range("B" & i)) = Len(Replace(Range("B" & i), " ", ""))) Then
            Range("C" & i) = Range("B" & i)
        Else
            Range("C" & i) = Replace(Range("B" & i), _
                String(Len(Range("B" & i)) - Len(Replace(Range("B" & i), " ", "")), " "), _
                String(9 - (InStr(Range("B" & i), " ") - 1), " "))
        End If
    Next i
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If

 

[RoryA]

1. You do not need your "ActiveSheet." references. If you leave those off, it assumes the "ActiveSheet". Also, by your use of Target, I am guessing this part of a Worksheet event procedure code. Those, by definition, run on the ActiveSheet.

Actually, they refer to the sheet containing the code, which may or may not be the active one.

 

[Joe4]

Actually, they refer to the sheet containing the code, which may or may not be the active one.

Good point. We would need the OP to set the record straight there. Most of the time when I see people use it, they don't actually need them.

And I am not entirely sure why, but the errors when away when I removed them.
So if they don't need them and can remove them, that is one solution.

 

[LuciakPL]

Thanks @Joe4, disabling events worked as a charm.
... and yes, RoryA is correct, I do need ActiveSheet references as this is part of a bigger, multi-sheet file with macros working among them.