Incidentally, Laurin, you can probably get to the same place, instead of
using the Case statements, doing something like this:
Output = Right("000000" & InputValue, 6)
All the values are 6 digits long, so in this way you would get the string in
the right length with the appropriate number of 0s prepended. I use this
type of techique all the time in my Access code.

"Laurin" wrote:


Laurin:
Thank you very much. I will give it a try. Does this not seem like an
awful lot of trouble, though, to fix something that should be quite
simple?
When you are in the spreadsheet, you can just go to Format Cells,
select
Text, and then type in all the leading 0s you want. I'm rather
astounded
that something I can do so easily in the interface seems
programmatically to
require a detour through Siberia.
Peter

I agree that it does. I'm not very experienced in programming so there
may be an easier way.

For the loop do this. First select the colum that you want to format
and name the range Data. Then use the following:


Code:
--------------------
Option Base 1

Sub Formatting()

Dim v as variant, Output() as string, I as Long
[Data].NumberFormat="@"
v = [Data]
Redim Output(ubound(v,1))
For I = 1 to ubound(v,1)
Select Case Len(v(I,1))
Case Is = 1: Output(I) = "00000" & v(I,1)
Case Is = 2: Output(I) = "0000" & v(I,1)
Case Is = 3: Output(I) = "000" & v(I,1)
Case Is = 4: Output(I) = "00" & v(I,1)
Case Is = 5: Output(I) = "0" & v(I,1)
Case Else: Output(I) = v(I,1)
End Select
Next I

[Data] = Application.worksheetfunction.transpose(Output)

End Sub
--------------------


--
Laurin
------------------------------------------------------------------------
Laurin's Profile: http://www.excelforum.com/member.php...o&userid=26832
View this thread: http://www.excelforum.com/showthread...hreadid=491854