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

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

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

Can't get VBA to work without select
 

Thank You very much,
Great!!!!!

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

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

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