format type in vbe

[pls123]

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

 

[Jacob Skaria]

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

 

[OssieMac]

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

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

 

[OssieMac]

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

 

[Jacob Skaria]

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

Msgbox Format(2,"000")

If this post helps click Yes

 

[Jacob Skaria]

Apologies.. you are right

 

[Dave Peterson]

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]

Thanks Dave. You are so right.

 

[pls123]

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]

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

 

[Harald Staff]

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

HTH. Best wishes Harald