Posted to microsoft.public.excel.programming
|
|
Excel 2007 VBA - Run-time error 1004
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
|