Range("A1:E3") contains 1 to 4 digits arithmetic numbers.
I want to format the range in "0000" format and still retain its
arithmetical properties.
This macro will duplicate the range in that format.
Can Range("A1:E1") be formatted "0000" in its own location?

Sub format()
Range("A5") = "=TEXT(A1,""0000"")"
Range("A5").AutoFill Destination:=Range("A5:A7"), Type:=xlFillDefault
Range("A5:A7").AutoFill Destination:=Range("A5:E7"), Type:=xlFillDefault
End Sub

Yes. Select the range A1:E3
Choose Format-Cells
Select CUSTOM
and enter the format as
0000

"danpt" wrote:

Range("A1:E3") contains 1 to 4 digits arithmetic numbers.
I want to format the range in "0000" format and still retain its
arithmetical properties.
This macro will duplicate the range in that format.
Can Range("A1:E1") be formatted "0000" in its own location?

Sub format()
Range("A5") = "=TEXT(A1,""0000"")"
Range("A5").AutoFill Destination:=Range("A5:A7"), Type:=xlFillDefault
Range("A5:A7").AutoFill Destination:=Range("A5:E7"), Type:=xlFillDefault
End Sub

While not recognized as number by the ISNUMBER function, your TEXT function
reutrns a value that is capable of being used in arithmetic.

However, if you just want to format the cells to give a certain display, it
would probably be easier to go to Format Cells - Number, custom
and then input
0000
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"danpt" wrote:

Range("A1:E3") contains 1 to 4 digits arithmetic numbers.
I want to format the range in "0000" format and still retain its
arithmetical properties.
This macro will duplicate the range in that format.
Can Range("A1:E1") be formatted "0000" in its own location?

Sub format()
Range("A5") = "=TEXT(A1,""0000"")"
Range("A5").AutoFill Destination:=Range("A5:A7"), Type:=xlFillDefault
Range("A5:A7").AutoFill Destination:=Range("A5:E7"), Type:=xlFillDefault
End Sub

Custom format A1:E1 as 0000 won't help?


Gord Dibben MS Excel MVP


On Wed, 20 May 2009 12:41:05 -0700, danpt
wrote:

Range("A1:E3") contains 1 to 4 digits arithmetic numbers.
I want to format the range in "0000" format and still retain its
arithmetical properties.
This macro will duplicate the range in that format.
Can Range("A1:E1") be formatted "0000" in its own location?

Sub format()
Range("A5") = "=TEXT(A1,""0000"")"
Range("A5").AutoFill Destination:=Range("A5:A7"), Type:=xlFillDefault
Range("A5:A7").AutoFill Destination:=Range("A5:E7"), Type:=xlFillDefault
End Sub