ProperCase/UpperCase

[nygaaard]

Hi!
I have a field in Excel where I write in a customers adress.
I use a code for this so the text in the field will use propercase.
So if I write in "streetname 101" for example, the result will be "Streetname 101".
This works good, but I have a small problem that I wanted to check if it's possible to solve.

If I have a adress with a letter after the streetnumber, for example "Streetname 101B", then propercase will make it "Streetname 101b".
Is it possible somehow to alter the code to always write letters after the streetnumber in uppercase?

This is the code I'm using right now:

    If Not Application.Intersect(Me.Range("A12:A13"), Target) Is Nothing Then
    If IsNumeric(Target.Value) = False Then
        Application.EnableEvents = False
        'Target.Value = StrConv(Target.Text, vbLowerCase)
        'Target.Value = StrConv(Target.Text, vbUpperCase)
        Target.Value = StrConv(Target.Text, vbProperCase)
        Application.EnableEvents = True
    End If
    End If

 

[steve the fish]

See if this works..

If Not Application.Intersect(Me.Range("A12:A13"), Target) Is Nothing Then
    myCount = Len(Target.Value) - Len(Replace(Target.Value, " ", ""))
    If myCount > 0 Then
        arr = Split(Target.Value, " ")
        For i = LBound(arr) To UBound(arr)
            If Not IsNumeric(Left(arr(i), 1)) Then
                arr(i) = StrConv(arr(i), vbProperCase)
            End If
        Next
        Target.Value = Join(arr, " ")
    Else
       Target.Value = StrConv(Target.Value, vbProperCase)
    End If
End If

 

[nygaaard]

See if this works..

If Not Application.Intersect(Me.Range("A12:A13"), Target) Is Nothing Then
    myCount = Len(Target.Value) - Len(Replace(Target.Value, " ", ""))
    If myCount > 0 Then
        arr = Split(Target.Value, " ")
        For i = LBound(arr) To UBound(arr)
            If Not IsNumeric(Left(arr(i), 1)) Then
                arr(i) = StrConv(arr(i), vbProperCase)
            End If
        Next
        Target.Value = Join(arr, " ")
    Else
       Target.Value = StrConv(Target.Value, vbProperCase)
    End If
End If

Hi!
I get a runtime-error "1004" and the whole Excel-file freezes and behaves weird.
"The method 'Replace' in the object 'Range' failed."

 

[nygaaard]

Hi!
I get a runtime-error "1004" and the whole Excel-file freezes and behaves weird.
"The method 'Replace' in the object 'Range' failed."

Anyone know what this problem can mean?
I have no clue.