Change text in a range to lower case

JKendall

New Member
Joined
Jun 28, 2012
Messages
16
Hi all,

I want to be able to run a macro to cycle down column B.
Column B is a list of names, first and last in the form of Smith, John
Unfortunately not every cell is populated as we go down the column so blank cells would have to be missed.

The complication, and what I want to do is make sure that all names are formatted in the same way.

Here is an example of what i have:


Smith, John
SMITH, John
SMITH, JOHN

smith, JOHN
Smith, John


I need it to read:

Smith, John
Smith, John
Smith, John

Smith, John
Smith, John


The spaces can remain. I want to keep the names in the same cell they are currently in. If this prooves difficult I would be happy to have all names in the format of smith, john.


My current code is as follows:

Sub Test2()
Range("B3").Select
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
If ActiveCell.Value = LCase(ActiveCell) Then
ActiveCell.Value = LCase(ActiveCell)
ElseIf ActiveCell.Value = UCase(ActiveCell) Then
ActiveCell.Value = _
ActiveCell.Value = LCase(ActiveCell)
ElseIf ActiveCell.Value = Application.WorksheetFunction _
.Proper(ActiveCell) Then
ActiveCell.Value = LCase(ActiveCell)
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub


This seems to scan my range and change some of the names but it doesn't format everything correctly. Any help would be much appreciated.

Thanks
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sorted it, thanks VoG :)

In case you might be interested, here is a non-looping macro that will change all the cells in the selected range (whether one or more cells, or one or more columns, so long as the range is contiguous)...
Code:
Sub MakeProperCase()
  Dim R As Range
  On Error GoTo Cancelled
  Set R = Application.InputBox("Click in the column to change", Type:=8)
  Set R = Intersect(ActiveSheet.UsedRange, R)
  R = Evaluate("=IF(" & R.Address & "="""","""",PROPER(" & R.Address & "))")
Cancelled:
End Sub
 
Upvote 0

Forum statistics

Threads
1,215,852
Messages
6,127,313
Members
449,374
Latest member
analystvar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top