CODEWITHSUNDEEP
vbaexcelcase-sensitivecase-insensitive
ADrex
ADrex

Reputation: 105

Why this code is making the codes change to Proper case?

This code is translating all the words in a cell, but only the first should be forced to Proper case, the other words should keep the case written by the user, but instead it is forcing the first word to proper case and all the other words in the cell to lower case. All the other words should mantain its original case. 

Sub TraAdd()

    Dim translate As Object 'scritping.Dictionary

    Set translate = CreateObject("Scripting.Dictionary")

    translate("modens") = "modems"
    translate("Modens") = "Modems"
    translate("modens,") = "modems,"
    translate("Modens,") = "Modems,"
    translate("Fruteira,") = "Fruit bowl,"
    translate("fruteira,") = "fruit bowl,"
    translate("Fruteira") = "Fruit bowl"
    translate("fruteira") = "fruit bowl"
    translate("muletas") = "crutches"
    translate("Muletas") = "Crutches"
    translate("muletas,") = "crutches,"
    translate("Muletas,") = "Crutches,"


    Dim Words As Variant
    Dim I As Integer
    Words = Split(LCase(activecell.Value))


    For I = LBound(Words) To UBound(Words)
        If translate(Words(I)) <> "" Then Words(I) = translate(Words(I))
    Next
    activecell.Value = Join(Words)
    activecell.Value = Ucase$(Left$(activecell.Value, 1)) & Right$(activecell.Value, Len(activecell.Value) - 1)

End Sub

Any ideas?

Upvotes: 0

Views: 46

Answers (1)

Instant Breakfast
Instant Breakfast

Reputation: 1445

You have made all of your content lowercase when you split it into an array.

Remove LCase when you split the cell content to Words and it should work as you intend:

Words = Split(activecell.Value)

Upvotes: 2

Related Questions