Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER CASE
How do I conver one column to upper case in a macro.
any help would be appreciated thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER CASE
Jason, here is one way, select your range and run this
Sub CAPS() 'select range and run this to change to all CAPS Dim cel As Range For Each cel In Intersect(Selection, _ ActiveSheet.UsedRange) cel.Formula = UCase$(cel.Formula) Next End Sub -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "JASON" wrote in message ... How do I conver one column to upper case in a macro. any help would be appreciated thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER CASE
Jason,
following should work in most situations. if 1 cell is selected it picks the current region, else it processes the text values in the selection. Sub MakeUpper() Dim c As Range on error resume next 'to avoid prob if no text cells are found If TypeName(Selection) < "Range" Then Beep: Exit Sub If Selection.Count = 1 Then Selection.CurrentRegion.Select With Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) For Each c In .Cells c = UCase(c) Next End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "JASON" wrote: How do I conver one column to upper case in a macro. any help would be appreciated thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER CASE
You've made a single cell selection expand to the current region.
That is the default and exactly what I would try to avoid. The important thing is that you have limited the cells to the used range and prevented formulas from being converted to values. Paul's solution prevents formulas from being changed to values by assigning a formula to a formula, either way prevents loss of formulas.. The following includes a few more things to make the macro run faster, like turning off screen updating and calculation. http://www.mvps.org/dmcritchie/excel/proper.htm#upper --- 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 "keepitcool" Jason, following should work in most situations. if 1 cell is selected it picks the current region, else it processes the text values in the selection. Sub MakeUpper() Dim c As Range on error resume next 'to avoid prob if no text cells are found If TypeName(Selection) < "Range" Then Beep: Exit Sub If Selection.Count = 1 Then Selection.CurrentRegion.Select With Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) For Each c In .Cells c = UCase(c) Next End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "JASON" wrote: How do I conver one column to upper case in a macro. any help would be appreciated thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UPPER CASE
Here a solution http://members.lycos.co.uk/shahweb
Regards, John. "JASON" ha scritto nel messaggio ... How do I conver one column to upper case in a macro. any help would be appreciated thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert lower to upper case automatically without using UPPER | Excel Discussion (Misc queries) | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) |