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' :thumb

[georgiboy]

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

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