Loop through cells in columns..

[Jake]

Hi,

I have a sheet with two columns of names, entered in uppercase letters.

I need to loop through a given range of cells and change the case
permanently to normal 'name' convention (first character uppercase and
the rest lowercase) for each word in each cell.

Thanks a LOT if someone has a quick solution for this

regards

Jake

 

[Guest]

I think you don't need a macro for this. Follow the below steps
Assume that your names are in column A. the name are entered like this, for
example my name PRANAV VAIDYA (first name & last name)

Say in column C enter this formula
=PROPER(A1)

Drag this formula tll end of your list
The copy and paste special values the names.

 

[Guest]

Jake,

Right click the sheet tab, view code and paste this in and run it.

Sub sonic()
Set myrange = Range("A1:B100") 'Change to suit
For Each c In myrange
c.Value = Application.WorksheetFunction.Proper(c.Value)
Next
End Sub

Mike

 

[Dallman Ross]

I have a sheet with two columns of names, entered in uppercase
letters.

I need to loop through a given range of cells and change the
case permanently to normal 'name' convention (first character
uppercase and the rest lowercase) for each word in each cell.

Jake,

Names are hard. There are zillions of exceptions. But for a
"quick solution" as you asked:

Sub TitleCase()

Dim myCell As Range
Dim myRight As String

For Each myCell In ActiveSheet.UsedRange
myRight = Right(myCell, Len(myCell) - 1)
myCell = UCase(Left(myCell, 1)) & LCase(myRight)
Next
End Sub


Needs embellishment for all sorts of aforesaid exceptions
(e.g., John Van der Kamp) and perhaps needs error handling
added.

=dman=