Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
I have this code that works fine only if I am on worksheet(1),
If I am on another sheet it does not work I get runtime error 1004 Here it is #Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub# What am I missing, so the code will work without having to select the sheet? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
Sub BoldCells()
Dim r As Range Dim i As Range Dim w As Worksheet Set w = Worksheets(1) Set r = w.Range("A1", w.Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub Note that in the set r = statement we need to define to which worksheet each Range() function refers. -- Gary''s Student - gsnu2007d "D." wrote: I have this code that works fine only if I am on worksheet(1), If I am on another sheet it does not work I get runtime error 1004 Here it is #Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub# What am I missing, so the code will work without having to select the sheet? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
If you had used Rows.Count, you would have gotten away with it (but you
sould properly qualify it Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A" & Rows.Count).End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D." wrote in message ... I have this code that works fine only if I am on worksheet(1), If I am on another sheet it does not work I get runtime error 1004 Here it is #Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub# What am I missing, so the code will work without having to select the sheet? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
Thank You very much,
Great!!!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
For completeness, you should also explicitly reference r.Cells:
For Each i In r.Cells - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gary''s Student" wrote in message ... Sub BoldCells() Dim r As Range Dim i As Range Dim w As Worksheet Set w = Worksheets(1) Set r = w.Range("A1", w.Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub Note that in the set r = statement we need to define to which worksheet each Range() function refers. -- Gary''s Student - gsnu2007d "D." wrote: I have this code that works fine only if I am on worksheet(1), If I am on another sheet it does not work I get runtime error 1004 Here it is #Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub# What am I missing, so the code will work without having to select the sheet? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
On Feb 4, 6:54 am, "Jon Peltier"
wrote: For completeness, you should also explicitly reference r.Cells: For Each i In r.Cells - Jon ------- Okay, Thanks for that |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't get VBA to work without select
Not if a chart sheet was the active sheet. And it would give you the last
filled cell in column A of the active sheet, not Worksheets(1). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bob Phillips" wrote in message ... If you had used Rows.Count, you would have gotten away with it (but you sould properly qualify it Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A" & Rows.Count).End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D." wrote in message ... I have this code that works fine only if I am on worksheet(1), If I am on another sheet it does not work I get runtime error 1004 Here it is #Sub BoldCells() Dim r As Range Dim i As Range Set r = Worksheets(1).Range("A1", Range("A65536").End(xlUp)) For Each i In r i.Font.Bold = Not i.Font.Bold Next i End Sub# What am I missing, so the code will work without having to select the sheet? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in excel - arrow keys won't work & cursor will not select a cell | Excel Discussion (Misc queries) | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
master select page field for many pivots in one work book | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) |