Change case

[Guest]

Is there a way to change all the entries in a column from lower case to
upper case?

 

[Guest]

In Excel you can use the formulas
Upper & Lower

In VBA you can use
UCase & LCase

It depends houw you want to approach the problem...

 

[Guest]

I just tried entering the =UPPER() formula for the column. It works on a new
entry, and unless I am not using the syntax correctly, it does not change
existing entries.

Column name is UMB

John

 

[Ron de Bruin]

Hi

See :

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm


Here are a few macro's for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub Lowercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = LCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Guest]

Upper only works on a single cell. In a column next to the one you want to
convert add the formula (assuming the data is in column A and the desired
result will be in B or where ever the formula is entered) and copy the
formula down...

=Upper(A1)

Which will convert the contents of Cell A1 to upper case (the result will be
in B1)

 

[PCLIVE]

This would work for column A

Sub test2()

Range("A:A").Select
For Each cell In Range("A:A")
cell.Value = UCase(cell.Value)
Next cell
End Sub

You might want to modify this a little in order to keep it from checking
cells below the point where your data stops.


Sub test2()
For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Value = UCase(cell.Value)
Next cell

End Sub

You can adjust the column reference as necessary. My example is using
column A.
HTH,
Paul

 

[Ron de Bruin]

Hi PCLIVE

Warning : If there are formulas in the range they will be values after you run the macro.
Use the code examples I posted to avoid that problem

 

[PCLIVE]

Thanks Ron,

I just assumed that since the poster mentioned "Entries" in a column, that
these would be manual entries. But you're correct. Thanks for pointing
that out.

Paul