Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing case
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing case
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) "Jeff" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing case
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Bob Phillips" wrote in message ... Jeff, Dim cell As Range For Each cell In Range("A1:A5") cell.Value = UCase(cell.Value) Next cell "Jeff" wrote 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing case
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/ *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing from upper case to lower case | Excel Discussion (Misc queries) | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing Upper case to Proper Case | Excel Worksheet Functions | |||
changing lower case to upper case | Excel Discussion (Misc queries) |