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:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    With Range("B" & i)
        .Value = StrConv(.Value, vbProperCase)
    End With
Next i
End Sub
 

JKendall

New Member
Joined
Jun 28, 2012
Messages
16

ADVERTISEMENT

Wow! That's awesome! It actually works correctly!

So just to clear up a few queries....I guess this will always search Column B, and it will search all the document regardless of how many rows are used in Column B?


Thankyou so much!!!
 

JKendall

New Member
Joined
Jun 28, 2012
Messages
16

ADVERTISEMENT

VoG,

That's been perfect, thanks for your help on that one! Seems to be working great.

I've decided I want to make a bit of an improvement now however.

The same code as before yet I want to be able to add a combo box in to my page so that the user can select a column between A-H and as they select it, it would run the same macro on the column that has been selected.

Does that make any sense?

Thanks,
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Give this a go

Code:
Sub atest()
Dim LR As Long, i As Long
Dim r As Range, col As Long
Set r = Application.InputBox("Click in the column to change", Type:=8)
col = r.Column
LR = Cells(Rows.Count, col).End(xlUp).Row
For i = 3 To LR
    With Cells(i, col)
        .Value = StrConv(.Value, vbProperCase)
    End With
Next i
End Sub
 

JKendall

New Member
Joined
Jun 28, 2012
Messages
16
That seems to work great! Thanks once again. One more quick question for you....if I incorporate something like the following will this catch the error that is given if the user hits "Cancel" ? Thanks...

On Error GoTo ErrorHandler

ErrorHandler:
Exit Sub
 

JKendall

New Member
Joined
Jun 28, 2012
Messages
16
Because this will be used by someone who is completely unfamiliar with macros I would like to end the macro and revert back to the spreadsheet without giving an option to debug...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,817
Messages
5,598,275
Members
414,221
Latest member
MD1222

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
Top