Object Error
 

Greetings,

When I run the macro below, everything works fine until I get to the .
NumberFormat line. Then I get an error message that says Object required.
What I am trying to do is remove any colors and erase the cells contents in
the range, and then set the format to numeric with commas and no zeros.

How can I make this work?

Thanks in advance for your help!

Ray

Sub ResetSheet()
Dim bclro As Long

Application.ScreenUpdating = False ' turn off screen updating
bclro = Cells(65536, 1).End(xlUp).row
With Range(Cells(30, 1), Cells(bclro, 55))
.Interior.ColorIndex = xlNone
.Delete
.NumberFormat = "#,##0"
End With

Object Error
 

Ray,

In your code, you've Deleted the cell in question, so then the
With statement is pointing at Nothing when you try to use the
NumberFormat property. Rather than use Delete, you probably want
to use Clear.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ray Batig" wrote in message
nk.net...
Greetings,

When I run the macro below, everything works fine until I get
to the .
NumberFormat line. Then I get an error message that says Object
required.
What I am trying to do is remove any colors and erase the cells
contents in
the range, and then set the format to numeric with commas and
no zeros.

How can I make this work?

Thanks in advance for your help!

Ray

Sub ResetSheet()
Dim bclro As Long

Application.ScreenUpdating = False ' turn off screen updating
bclro = Cells(65536, 1).End(xlUp).row
With Range(Cells(30, 1), Cells(bclro, 55))
.Interior.ColorIndex = xlNone
.Delete
.NumberFormat = "#,##0"
End With

Object Error
 

Thanks Chip,

I used the recorder to set up the macro and pushed the delete key. Now I
know better....

Ray

Chip Pearson wrote in message
...
Ray,

In your code, you've Deleted the cell in question, so then the
With statement is pointing at Nothing when you try to use the
NumberFormat property. Rather than use Delete, you probably want
to use Clear.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ray Batig" wrote in message
nk.net...
Greetings,

When I run the macro below, everything works fine until I get
to the .
NumberFormat line. Then I get an error message that says Object
required.
What I am trying to do is remove any colors and erase the cells
contents in
the range, and then set the format to numeric with commas and
no zeros.

How can I make this work?

Thanks in advance for your help!

Ray

Sub ResetSheet()
Dim bclro As Long

Application.ScreenUpdating = False ' turn off screen updating
bclro = Cells(65536, 1).End(xlUp).row
With Range(Cells(30, 1), Cells(bclro, 55))
.Interior.ColorIndex = xlNone
.Delete
.NumberFormat = "#,##0"
End With

Object Error
 

The macro recorder should emit a ClearContents command rather
than Delete. At least it does for me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ray Batig" wrote in message
ink.net...
Thanks Chip,

I used the recorder to set up the macro and pushed the delete
key. Now I
know better....

Ray

Chip Pearson wrote in message
...
Ray,

In your code, you've Deleted the cell in question, so then the
With statement is pointing at Nothing when you try to use the
NumberFormat property. Rather than use Delete, you probably
want
to use Clear.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ray Batig" wrote in message
nk.net...
Greetings,

When I run the macro below, everything works fine until I
get
to the .
NumberFormat line. Then I get an error message that says
Object
required.
What I am trying to do is remove any colors and erase the
cells
contents in
the range, and then set the format to numeric with commas
and
no zeros.

How can I make this work?

Thanks in advance for your help!

Ray

Sub ResetSheet()
Dim bclro As Long

Application.ScreenUpdating = False ' turn off screen
updating
bclro = Cells(65536, 1).End(xlUp).row
With Range(Cells(30, 1), Cells(bclro, 55))
.Interior.ColorIndex = xlNone
.Delete
.NumberFormat = "#,##0"
End With