format type in vbe

  • Thread starter Thread starter pls123
  • Start date Start date
P

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
 
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
 
The following iserts the number as a number rather than converting it to text.

Range("A1") = 27
Range("A1").NumberFormat = "000"
 
Have you tested that Jacob? I can never get it to work unless the cell is
formatted to text.
 
This is a VB Script function...Try the below in immediate window...

Msgbox Format(2,"000")

If this post helps click Yes
 
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.
 
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..!
 
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
 
Sub test()
With Range("A1")
.NumberFormat = "000"
.Value = 12
End With
End Sub

HTH. Best wishes Harald
 
Back
Top