How can I apply a function (PROPER) to multiple cells at once?

[AGrozier]

have a "database" of names and addresses completely in uppercase letters and
need to format it with the PROPER function so I can use it to print address
labels. No problem doing this for one cell, but have thousands of names and
am wondering if there is a way to do this globally for a range of cells

 

[Gord Dibben]

From David McRithchie comes this macro.

Sub Proper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = Application.Proper(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[carlo]

You could do that in VBA:

Sub MyProper()

Application.ScreenUpdating = False

Dim myrange As Range
Set myrange = ActiveSheet.UsedRange

For Each cell_ In myrange
If cell_ <> "" Then
cell_.Value =
Application.WorksheetFunction.Proper(cell_.Value)
Debug.Print cell_.Address
End If
Next cell_

Application.ScreenUpdating = True

End Sub

you can change myrange to whatever you want.

hth

Carlo

 

[Tim Zych]

This works in one fell swoop:

Dim r As Range
Set r = ActiveSheet.UsedRange
r.Value = Application.Proper(r.Value)