Can't get VBA to work without select

[D.]

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

 

[Gary''s Student]

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.

 

[Bob Phillips]

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.]

Thank You very much,
Great!!!!!

 

[Jon Peltier]

For completeness, you should also explicitly reference r.Cells:

For Each i In r.Cells

- Jon

 

[D.]

For completeness, you should also explicitly reference r.Cells:

For Each i In r.Cells

- Jon
-------

Okay, Thanks for that

 

[Jon Peltier]

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