Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Proper case/multiple words (2K3)

    I have a long column of names with a mixture Upper case, Lower case and Proper case strings. I have written a simple piece of code for columns with single words and this works fine but can't seem to see any resources for multiple word strings.

    I need MAGIC FOREST ROAD RUNNERS or magic forest road runners to read Magic Forest Road Runners.

    Here is an example of my "single word" code

    Sub ChangeCase()
    Dim RowCount As Integer
    Dim i As Integer

    RowCount = Range("A65536").End(xlUp).Row - 1
    MsgBox RowCount

    Range("A2").Select

    For i = 1 To RowCount

    ActiveCell = UCase(Left(ActiveCell.Value, 1)) & LCase(Right(ActiveCell.Value, Len(ActiveCell) - 1))


    ActiveCell.Offset(1, 0).Select

    Next i

    End Sub
    Jerry

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper case/multiple words (2K3)

    No Problems Jezza, just use the StrConv function so it reads like this:

    Sub ChangeCase()
    Dim RowCount As Integer
    Dim i As Integer

    RowCount = Range("A65536").End(xlUp).Row - 1
    MsgBox RowCount

    Range("A2").Select

    For i = 1 To RowCount


    ActiveCell = StrConv(ActiveCell.Value, vbProperCase)

    ActiveCell.Offset(1, 0).Select

    Next i

    End Sub


    Just be careful as it changes ALL words like of & and
    Jerry

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper case/multiple words (2K3)

    Does this do what you want?

    <code>
    Public Sub FixCase()
    Dim strWk As String
    Dim lRowCount As Long, I As Long, J As Long
    lRowCount = Range("A1").Offset(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count, 0).End(xlUp).Row - 1
    For I = 1 To lRowCount
    strWk = ""
    If (Range("A1").Offset(I, 0).HasFormula = False) And (IsNumeric(Range("A1").Offset(I, 0).Value) = False) _
    And (Range("A1").Offset(I, 0).Value <> "") Then
    strWk = Range("A1").Offset(I, 0).Value
    End If
    If strWk <> "" Then
    strWk = LCase(strWk)
    Mid(strWk, 1, 1) = UCase(Left(strWk, 1))
    J = InStr(strWk, " ") + 1
    Do While J < Len(strWk)
    Mid(strWk, J) = UCase(Mid(strWk, J, 1))
    J = InStr(J, (strWk & " "), " ") + 1
    Loop
    Range("A1").Offset(I, 0).Value = strWk
    End If
    Next I
    End Sub
    </code>


    Or you could simplify it a bit using StrConv like this:

    <code>
    Public Sub FixCase()
    Dim strWk As String
    Dim lRowCount As Long, I As Long, J As Long
    lRowCount = Range("A1").Offset(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count, 0).End(xlUp).Row - 1
    For I = 1 To lRowCount
    strWk = ""
    If (Range("A1").Offset(I, 0).HasFormula = False) And (IsNumeric(Range("A1").Offset(I, 0).Value) = False) _
    And (Range("A1").Offset(I, 0).Value <> "") Then
    strWk = StrConv(Range("A1").Offset(I, 0).Value, vbProperCase)
    End If
    If strWk <> "" Then
    Range("A1").Offset(I, 0).Value = StrConv(strWk, vbProperCase)
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper case/multiple words (2K3)

    Using all that cell selecting is very inefficient and will flash the screen a lot. Your code will also replace formulas with values which is ok if that is what you want to do.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper case/multiple words (2K3)

    Thanks Legare

    Sorry for the delay getting back to you, these were race results that needed to be published by the 10.00pm tonight and I had to have them sent through from the Chief Timekeeper, convert them and write a set of web pages with links to the documents and get them published on a web site <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    The whole thing was an annual "one off" so I wasn't too worried about screen updates and inefficiency at the time, I take your point but needs must when the Devil drives <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I have had a go at your code and can't seem to get it to work for me <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> it may be because I was stressed out here to really go into indepth but thanks for your time, I will have a play with it now.
    Jerry

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Proper case/multiple words (2K3)

    Proper() converts multiple words.
    My approach would be to have a shadow worksheet on another tab to take a straight copy of the original, but to use the Proper() function on the appropriate column. If necessary, you can then copy | Paste Special | Value back into the original worksheet.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper case/multiple words (2K3)

    I like, I like very much. A nice simple solution without code, thank you <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper case/multiple words (2K3)

    What problem did you have with my code? I did test it by copying the phrase from your original post and pasting it into A2 in a new workbook. I then copied it down to A40 and tested the code. Both versions of the code did work.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper case/multiple words (2K3)

    Not to worry Legare, it was my wrong doing, having found that I had to move onto column H, I didn't change one of the A1 references ......
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •