Can't get VBA to work without select

  • Thread starter Thread starter D.
  • Start date Start date
D

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
 
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.
 
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)
 
For completeness, you should also explicitly reference r.Cells:

For Each i In r.Cells

- Jon
 
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
 
Back
Top