Loop through All worksheets and change to Proper case

[KathCobb]

Hello All,

I am once again struggling with looping through all worksheets, when the code to run is beyond very basic. I have the code below that works great on whatever is the active worksheet to change anything in Column A to Proper Case

Sub ProperCase()
Dim rng As Range
    For Each rng In Range("A2", [a65536].End(xlUp)) 'Start the VBA loop
    rng = StrConv(rng.Text, vbProperCase)
Next
End Sub

What I need it to do is Loop through All Worksheets and run this code. I tried this:

Sub ProperCaseworksAlone()
Dim rng As Range
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
     With ws
         For Each rng In Range("A2", [a65536].End(xlUp)) 'Start the VBA loop
         rng = StrConv(rng.Text, vbProperCase)
     Next
Next
End Sub

I do not know why it does not work other than thee are two "For Each" Statements? I do not necessarily need Last Row or Used Range. All of Column A can be changed to Proper Case. If the cells are blank, it does no harm.

I would appreciate any and all advice on what I am doing wrong and how to correct it.

Thanks so much,

Kathy

[Paul_Hossler]

Your inner (rng) loop is only working on the activesheet since you did not specify ws for A1

Option Explicit

Sub ProperCaseworksAlone()
    Dim rng As Range
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            For Each rng In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))     ' <<<<<< note the dots on the .Cells to refer to the current ws
                rng = StrConv(rng.Text, vbProperCase)
            Next
        End With
    Next
    Application.ScreenUpdating = True
End Sub

[georgiboy]

Do we even need a range loop?

Sub test()    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        With ws.UsedRange.Resize(, 1)
            .Value = Application.Proper(.Value)
        End With
    Next ws
End Sub

[Paul_Hossler]

Do we even need a range loop?

Not really, but I took it a 'teaching opportunity'

[georgiboy]

Not really, but I took it a 'teaching opportunity'

Good point, get lost in the world of "Less is more"

[KathCobb]

I finally had a chance to try it out. Both options work great and YES thank you so much for the "teaching". I just cannot seem to grasp the loop through each worksheet concept and the changes that need to be made to code when using it.
Thank you both so much.

[Paul_Hossler]

1. If performance is problem, then .SpecialCells can return just the text cells in column A from the .UsedArea of the ws

2. For objects, such as the Range 'rng', that do NOT explicitly reference the parent, the current / active 'parent' is implied.

The .Parent of a .Range is a Worksheet

So Set rng = Range("A1") [no dot] will use the ActiveSheet, even inside a With / End With

And Set rng = ws.Range("A1") [dot] will always use Worksheet ws


If you're looping through worksheets (For Each ws in ...)

a. without the dot, it will always use the ActiveSheet regardless of the With ws / End With

b. with the dot, it will always use the explicitly specified Parent, in this case the ws because of the With /End With

Option Explicit

Sub ProperCaseworksAlone()
    Dim rng As Range
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            On Error Resume Next    '   in case thee are no text cells
            Set rng = Intersect(.UsedRange, .Columns(1)).SpecialCells(xlCellTypeConstants, xlTextValues)
            rng.Value = Application.Proper(rng.Value)
            On Error GoTo 0
        End With
    Next
   Application.ScreenUpdating = True
End Sub

[snb]

I see no performance issues with:

Sub M_snb()
   For Each it In Sheets
     it.UsedRange.Columns(1) = Application.Proper(it.UsedRange.Columns(1))
   Next
End Sub