Run-time error: '-2147417848 (80010108)' in macro-CodePudding

If I enter a word with a space in column A (for example: L3 , FMA , Nest , etc.), then it gives an error: "Run-time error: '-2147417848 (80010108)': Method 'HorizontalAlignment' of object'Range' failed". How can I fix this?

I have this macro:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range, v As Variant
    For Each rng In Target
        v = Split(rng, " ")
        If UBound(v) <> 1 Then Exit Sub
        If Right(rng, 1) <> "m" Then
            rng = v(1) & " " & v(0)
     NumberFormat = "@"
      With Target
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    With Target.Font
        .Name = "Calibri"
        .Size = 11
    End With
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

This macro converts the data to (any number)m and (any number)M be on the right.

For example:

L3 280M
500m FMA
Nest 475m
340m Pr6
720M uT10
etc.

Convert to:

L3 280M
FMA 500m
Nest 475m
Pr6 340m
uT10 720M
etc.

CodePudding user response：

Anytime you use Worksheet Change you need to disable the events or your event triggers itself causing an infinite loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim numberformat As String
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim rng As Range, v As Variant
    For Each rng In Target
        v = Split(rng, " ")
        If UBound(v) <> 1 Then GoTo cleanexit
        If Right(rng, 1) <> "m" Then
            rng = trim(v(1) & " " & v(0))
            numberformat = "@"
            With Target
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
            End With
            With Target.Font
                .Name = "Calibri"
                .Size = 11
            End With
        End If
    Next rng
cleanexit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub