In Excel 2007, a macro has the following at the beginning of the code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it has not.

For testing, I removed that line of code and ran the macro, it then again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been taken
care of in SP1 ?
Thanks.

I bet the worksheet is protected.

If your code is behind a worksheet, then those unqualified ranges refer to the
sheet owning the code--not any sheet that you activated.

If this doesn't help, you may want to provide more code and state where the
module is.

SG wrote:

In Excel 2007, a macro has the following at the beginning of the code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it has not.

For testing, I removed that line of code and ran the macro, it then again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been taken
care of in SP1 ?
Thanks.

--

Dave Peterson

This will break:

Range("A4", Range("A4").End(xlDown))

It should be

Range(Range("A4"), Range("A4").End(xlDown))

Even better to reference a sheet:

With Worksheets("Some Sheet")
.Range(.Range("A4"), .Range("A4").End(xlDown))
End With

Sometimes 2007 is a bit more finicky about these references.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dave Peterson" wrote in message
...
I bet the worksheet is protected.

If your code is behind a worksheet, then those unqualified ranges refer to
the
sheet owning the code--not any sheet that you activated.

If this doesn't help, you may want to provide more code and state where
the
module is.

SG wrote:

In Excel 2007, a macro has the following at the beginning of the code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the
range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it has
not.

For testing, I removed that line of code and ran the macro, it then again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been
taken
care of in SP1 ?
Thanks.

--

Dave Peterson

I use that first version lots of times in xl2003 and below.

I fired up VPC and xl2007 to test it with:

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Range("A4", Range("A4").End(xlDown))
Debug.Print myRng.Address
End Sub

And got this in the immediate window:
$A$4:$A$19


I put the code in a general module (no qualified ranges!) and it worked ok.

Are you sure it breaks in your version (<bg) of xl2007?

Jon Peltier wrote:

This will break:

Range("A4", Range("A4").End(xlDown))

It should be

Range(Range("A4"), Range("A4").End(xlDown))

Even better to reference a sheet:

With Worksheets("Some Sheet")
.Range(.Range("A4"), .Range("A4").End(xlDown))
End With

Sometimes 2007 is a bit more finicky about these references.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Dave Peterson" wrote in message
...
I bet the worksheet is protected.

If your code is behind a worksheet, then those unqualified ranges refer to
the
sheet owning the code--not any sheet that you activated.

If this doesn't help, you may want to provide more code and state where
the
module is.

SG wrote:

In Excel 2007, a macro has the following at the beginning of the code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the
range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it has
not.

For testing, I removed that line of code and ran the macro, it then again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been
taken
care of in SP1 ?
Thanks.

--

Dave Peterson

--

Dave Peterson

I tested it in 2003 and in 2007 and it broke. Just now I tested it in both
versions and it worked. I must have typed it wrong in my test module.

Sorry.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dave Peterson" wrote in message
...
I use that first version lots of times in xl2003 and below.

I fired up VPC and xl2007 to test it with:

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Range("A4", Range("A4").End(xlDown))
Debug.Print myRng.Address
End Sub

And got this in the immediate window:
$A$4:$A$19


I put the code in a general module (no qualified ranges!) and it worked
ok.

Are you sure it breaks in your version (<bg) of xl2007?

Jon Peltier wrote:

This will break:

Range("A4", Range("A4").End(xlDown))

It should be

Range(Range("A4"), Range("A4").End(xlDown))

Even better to reference a sheet:

With Worksheets("Some Sheet")
.Range(.Range("A4"), .Range("A4").End(xlDown))
End With

Sometimes 2007 is a bit more finicky about these references.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Dave Peterson" wrote in message
...
I bet the worksheet is protected.

If your code is behind a worksheet, then those unqualified ranges refer
to
the
sheet owning the code--not any sheet that you activated.

If this doesn't help, you may want to provide more code and state where
the
module is.

SG wrote:

In Excel 2007, a macro has the following at the beginning of the
code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the
debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the
range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it
has
not.

For testing, I removed that line of code and ran the macro, it then
again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use
this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been
taken
care of in SP1 ?
Thanks.

--

Dave Peterson

--

Dave Peterson

Whew!

I forgive you <vvbg.

Jon Peltier wrote:

I tested it in 2003 and in 2007 and it broke. Just now I tested it in both
versions and it worked. I must have typed it wrong in my test module.

Sorry.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Dave Peterson" wrote in message
...
I use that first version lots of times in xl2003 and below.

I fired up VPC and xl2007 to test it with:

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Range("A4", Range("A4").End(xlDown))
Debug.Print myRng.Address
End Sub

And got this in the immediate window:
$A$4:$A$19


I put the code in a general module (no qualified ranges!) and it worked
ok.

Are you sure it breaks in your version (<bg) of xl2007?

Jon Peltier wrote:

This will break:

Range("A4", Range("A4").End(xlDown))

It should be

Range(Range("A4"), Range("A4").End(xlDown))

Even better to reference a sheet:

With Worksheets("Some Sheet")
.Range(.Range("A4"), .Range("A4").End(xlDown))
End With

Sometimes 2007 is a bit more finicky about these references.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Dave Peterson" wrote in message
...
I bet the worksheet is protected.

If your code is behind a worksheet, then those unqualified ranges refer
to
the
sheet owning the code--not any sheet that you activated.

If this doesn't help, you may want to provide more code and state where
the
module is.

SG wrote:

In Excel 2007, a macro has the following at the beginning of the
code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the
debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the
range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it
has
not.

For testing, I removed that line of code and ran the macro, it then
again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use
this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been
taken
care of in SP1 ?
Thanks.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson