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