format type in vbe
 

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

format type in vbe
 

Use the FORMAT function.. Try the below code within a mcro or from the
immediate window..


Range("A1") = Format(12,"000")


If this post helps click Yes
---------------
Jacob Skaria


"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

format type in vbe
 

The following iserts the number as a number rather than converting it to text.

Range("A1") = 27
Range("A1").NumberFormat = "000"

--
Regards,

OssieMac


"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

format type in vbe
 

Have you tested that Jacob? I can never get it to work unless the cell is
formatted to text.

--
Regards,

OssieMac


"Jacob Skaria" wrote:

Use the FORMAT function.. Try the below code within a mcro or from the
immediate window..


Range("A1") = Format(12,"000")


If this post helps click Yes
---------------
Jacob Skaria


"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

format type in vbe
 

This is a VB Script function...Try the below in immediate window...

Msgbox Format(2,"000")

If this post helps click Yes
---------------
Jacob Skaria


"OssieMac" wrote:

Have you tested that Jacob? I can never get it to work unless the cell is
formatted to text.

--
Regards,

OssieMac


"Jacob Skaria" wrote:

Use the FORMAT function.. Try the below code within a mcro or from the
immediate window..


Range("A1") = Format(12,"000")


If this post helps click Yes
---------------
Jacob Skaria


"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

format type in vbe
 

Apologies.. you are right
--
If this post helps click Yes
---------------
Jacob Skaria


"OssieMac" wrote:

Have you tested that Jacob? I can never get it to work unless the cell is
formatted to text.

--
Regards,

OssieMac


"Jacob Skaria" wrote:

Use the FORMAT function.. Try the below code within a mcro or from the
immediate window..


Range("A1") = Format(12,"000")


If this post helps click Yes
---------------
Jacob Skaria


"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

format type in vbe
 

You may want to reverse the order of these commands.

with range("A1")
.numberformat = "000"
.value = 27
end with

If the original/existing format of the cell were Text (@), then this order will
avoid a problem.

OssieMac wrote:

The following iserts the number as a number rather than converting it to text.

Range("A1") = 27
Range("A1").NumberFormat = "000"

--
Regards,

OssieMac

"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

--

Dave Peterson

format type in vbe
 

Thanks Dave. You are so right.
--
Regards,

OssieMac


"Dave Peterson" wrote:

You may want to reverse the order of these commands.

with range("A1")
.numberformat = "000"
.value = 27
end with

If the original/existing format of the cell were Text (@), then this order will
avoid a problem.

OssieMac wrote:

The following iserts the number as a number rather than converting it to text.

Range("A1") = 27
Range("A1").NumberFormat = "000"

--
Regards,

OssieMac

"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

--

Dave Peterson

format type in vbe
 

tx all i will try.. tomorrow now in italy i have to go to sleep !!
exactly i need it for composing the Fname with witch i will save the
workbook..

so it could be something like this..
myvalue = Range("a7").numberformat = 000

if i dont use this it will save 27 instead than 027..
its a little thing but it will make more to read clear a long list of files

tx..!


"OssieMac" wrote:

Thanks Dave. You are so right.
--
Regards,

OssieMac


"Dave Peterson" wrote:

You may want to reverse the order of these commands.

with range("A1")
.numberformat = "000"
.value = 27
end with

If the original/existing format of the cell were Text (@), then this order will
avoid a problem.

OssieMac wrote:

The following iserts the number as a number rather than converting it to text.

Range("A1") = 27
Range("A1").NumberFormat = "000"

--
Regards,

OssieMac

"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo

--

Dave Peterson

format type in vbe
 

If you want to use it for a filename then you might want to set the number
format to text. Using Dave's code method but setting to text in lieu of a
numeric, it would be like this.

With Range("A1")
.NumberFormat = "@"
.Value = Format(27, "000")
End With

--
Regards,

OssieMac

format type in vbe
 

Sub test()
With Range("A1")
.NumberFormat = "000"
.Value = 12
End With
End Sub

HTH. Best wishes Harald

"OssieMac" wrote in message
...
Have you tested that Jacob? I can never get it to work unless the cell is
formatted to text.

--
Regards,

OssieMac


"Jacob Skaria" wrote:

Use the FORMAT function.. Try the below code within a mcro or from the
immediate window..


Range("A1") = Format(12,"000")


If this post helps click Yes
---------------
Jacob Skaria


"pls123" wrote:

hi all users !!
how can i specify the format type in vbe
for writing in a cell a number ??
i need a 000 style format
so 27 is a 027
ty for help !!
paolo