Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
specify cell selection in a marco
I found a useful macro online but it only selects cells A1:A5 and I need to
modify it so I can select a range of cells by highlighting them, and then run the macro. I tried to delete part of the syntax but just get error messages. The macro I have now is: Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A5") ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub Can anyone fix this for me? thanx - chu |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
specify cell selection in a marco
If you can select the range first, it might make it simpler:
Sub Uppercase() Dim x as Range ' Loop to cycle through each cell in the specified range. For Each x In Selection.Cells ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub chubach wrote: I found a useful macro online but it only selects cells A1:A5 and I need to modify it so I can select a range of cells by highlighting them, and then run the macro. I tried to delete part of the syntax but just get error messages. The macro I have now is: Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A5") ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub Can anyone fix this for me? thanx - chu -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
specify cell selection in a marco
Thanks, it works great. However, if I select the entire workbook by clicking
in the upper, lefthand part of the row/column headings it causes the application to freeze or keep looping. Is there a way to make the selection this way and have it stop after the last active cell? "Dave Peterson" wrote: If you can select the range first, it might make it simpler: Sub Uppercase() Dim x as Range ' Loop to cycle through each cell in the specified range. For Each x In Selection.Cells ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub chubach wrote: I found a useful macro online but it only selects cells A1:A5 and I need to modify it so I can select a range of cells by highlighting them, and then run the macro. I tried to delete part of the syntax but just get error messages. The macro I have now is: Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A5") ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub Can anyone fix this for me? thanx - chu -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
specify cell selection in a marco
You could limit the loop to just the usedrange, but if you're going to limit
them, you might as well just loop through the cells that have text constants. Option Explicit Sub Uppercase() Dim x As Range Dim myRng As Range Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "no constants selected" Else ' Loop to cycle through each cell in the specified range. For Each x In Selection.Cells ' Change the text in the range to uppercase letters. x.Value = UCase(x.Value) Next x End If End Sub chubach wrote: Thanks, it works great. However, if I select the entire workbook by clicking in the upper, lefthand part of the row/column headings it causes the application to freeze or keep looping. Is there a way to make the selection this way and have it stop after the last active cell? "Dave Peterson" wrote: If you can select the range first, it might make it simpler: Sub Uppercase() Dim x as Range ' Loop to cycle through each cell in the specified range. For Each x In Selection.Cells ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub chubach wrote: I found a useful macro online but it only selects cells A1:A5 and I need to modify it so I can select a range of cells by highlighting them, and then run the macro. I tried to delete part of the syntax but just get error messages. The macro I have now is: Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A5") ' Change the text in the range to uppercase letters. x.Value = UCase(x.value) Next End Sub Can anyone fix this for me? thanx - chu -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add "left align across selection" cell formatting | Excel Worksheet Functions | |||
Selection starting from active cell | Excel Discussion (Misc queries) | |||
cell selection appears locked. How do I unlock it. | Excel Worksheet Functions | |||
Line selection from a cell script through a VBA macro | Setting up and Configuration of Excel | |||
Cell Selection after "Enter" | Excel Discussion (Misc queries) |