| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to type format time in decimal format & calculate | Excel Discussion (Misc queries) | |||
| Format Cell As You Type | Excel Programming | |||
| Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) | |||
| Can I set the numerical type of Cell to Hex format | Excel Discussion (Misc queries) | |||
| UDF, Category, Data Type, Format, Using | Excel Programming | |||
Linear Mode
