VBA Proper Case Question

[Shiseiji]

Uh, help please. I've got the simple macro to insert a column, use the Proper function, then copy/paste special over the original column and delete the inserted column. Trying to understand and use the vba examples I find is beyond by basic skill set . . . worst case I'll just use what is working, but I'm trying to learn how to avoid in VBA using an inserted column to run the function.

Lower case I managed to find the code and see how to use it:

' Find the last row of records for reference
Range("A1").Select
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A2:A" & LastRow).Value = LCase(Range("A2:A" & LastRow).Value)

But darned if I can figure out out to convert the code I find that has a user box into code that will simply run on my range . . .

I've tried two different pieces of code, but I can't figure out how to set my range for it to run on my without user input . . .

Sub ChangeCase()
    Dim Rng As Range
    On Error Resume Next
    R
    Err.Clear
    Application.EnableEvents = False
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
             xlTextValues).Cells
        If Err.Number = 0 Then
           ' Rng.Value = StrConv(Rng.Text, vbUpperCase)
           ' Rng.Value = StrConv(Rng.Text, vbLowerCase)
           ' Rng.Value = StrConv(Rng.Text, vbProperCase)
        End If
    Next Rng
    Application.EnableEvents = True
End Sub

or

Sub ConvertCase( )
Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long


    'Set variable to needed cells
    If Selection.Cells.Count = 1 Then
        Set rAcells = ActiveSheet.UsedRange
    Else
    Set rAcells = Selection
    End If


    On Error Resume Next 'In case of NO text constants.
    'Set variable to all text constants
    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)


    If rAcells Is Nothing Then
    MsgBox "Could not find any text."
    On Error GoTo 0
    Exit Sub
    End If


    lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _
    vbYesNoCancel, "OzGrid.com")
    If lReply = vbCancel Then Exit Sub


    If lReply = vbYes Then ' Convert to Upper Case
    For Each rLoopCells In rAcells
        rLoopCells = StrConv(rLoopCells, vbUpperCase)
    Next rLoopCells
    Else ' Convert to Proper Case
    For Each rLoopCells In rAcells
        rLoopCells = StrConv(rLoopCells, vbProperCase)
    Next rLoopCells
    End If
End Sub

TIA to anyone able/willing to provide assistance . . .

 

[mole999]

Is the three single apostrophes intentional

        If Err.Number = 0 Then
           ' Rng.Value = StrConv(Rng.Text, vbUpperCase)
           ' Rng.Value = StrConv(Rng.Text, vbLowerCase)
           ' Rng.Value = StrConv(Rng.Text, vbProperCase)
        End If

 

[Shiseiji]

Yes. It's written to be mulit-function capable. Un rem the desired action. In my case proper case.

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub MakeProper()
  Selection = Evaluate(Replace("IF(@="""","""",[B][COLOR="#FF0000"]PROPER[/COLOR][/B](@))", "@", Selection.Address))
End Sub[/td]
[/tr]
[/table]

EDIT NOTE
-----------------------

Yes. It's written to be mulit-function capable. Un rem the desired action. In my case proper case.

In that case, replace the PROPER keyword above with either UPPER or LOWER as needed.

 

[Shiseiji]

Sir, thank you! I have a colleague who is rather, uh, challenged in several areas "Can you help me turn on spell check in my Outlook?" Due to several factors a file tidy-up I routinely do will now be his project. Thanks for helping me to set him up the best I can to succeed. I'm on to what I hope is my last question on this file, but it will be in a new thread.

Thanks again,

Ron

 

[Shiseiji]

I wear my ignorance openly, excel is not my chosen career, its a means to an ends

Love it!

 

[Rick Rothstein]

Can you help me turn on spell check in my Outlook?"

Sorry, I know next to nothing about Outlook. However, I was under the impression that spell-checking was always on in Outlook... considering you asked that question, though, I presume I am wrong about that.

 

[igold]

Outlook=> File => Options => Mail scroll to "Compose messages", check the box that says "Always check spelling before sending".

 

[Shiseiji]

I thought it was a default setting as I don't recall ever having to manually turn it on. But he needed help, I was happy to provide. To me it was like teaching someone to disassemble a bike wheel. From that I can usually determine what their skill set is. Not good, not bad, just what is. Hence my work to try to set him up to succeed as best I can. And I get to learn something too.

Ron

 

[igold]

I guess that means you were able to turn Spell Check on for your bicycle challenged friend...