changing case

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Lets say column A has the data below:

Row1: john SMITH
Row2: Jane DOE
Row3: tom brown
Row4: john doe
Row5: TOM smith

What I need is VBA code to select the range above and
change all letters to uppper-case. Thanks
 
Jeff,

Dim cell As Range

For Each cell In Range("A1:A5")
cell.Value = UCase(cell.Value)
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Jeff,
Might I suggest that Proper (Title) case looks a lot better.
http://www.mvps.org/dmcritchie/excel/proper.htm

Use of all uppercase for everything should have gone out
with the keypunch machines.

If you want to compare text in a macro without regard to letter case you can use
If ucase(var1) = ucase(var2) then ...
as mentioned by Bob,
or faster using a text compare (1 or vbTextCompare)
If StrComp(var1, var2, 1) = 0 then ... ' Returns 0 if values are textually equal.
 
Hi Jeff,

Try this,

This will Change Text as under

Lower case to UPPERCASE
UPPERCASE to Title Case
Title Case to lowercase

Sub SelectCase()
'Assign Shortcut key
Dim C As Range, rng As Range, cc As Integer
If Selection.Count = 1 Then
Set rng = Selection.SpecialCells(xlCellTypeConstants).Cells
Else
Set rng = Selection
End If
Set C = rng(1)
Select Case True
Case C = LCase(C)
cc = 1
Case C = UCase(C)
cc = 2
Case Else
cc = 3
End Select
Application.EnableCancelKey = xlErrorHandler
On Error GoTo xit
Application.EnableEvents = 0
For Each C In rng
With C
.Formula = Choose(cc, UCase(.Formula),
Application.Proper(.Formula), LCase(.Formula))
End With
Next C
xit:
Application.EnableEvents = 1
End Sub


For a free add-ins download from below site.

Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
 
Back
Top