"000" reverts back to "0" even with a string
in [Programming]
|
Prev: Entry data sheet automaticlly updates or adds data to differant sh
Next: creating a pivot with external data source (from access)
From: Philosophaie on 21 May 2010 23:32 I want to have a 4 digit number in all cases but some have 0 thru 3. This is how I attempted to solve it: dim x(3) as string x(1)=234 x(2)=67 x(3)=5 If Len(x(m)) = 1 Then x(m) = 000 & x(m) If Len(x(m)) = 1 Then x(m) = 00 & x(m) If Len(x(m)) = 1 Then x(m) = 0 & x(m) the problem is when you enter "000" in for the result it automatically reverts back to "0"
From: Jim Cone on 21 May 2010 23:46 They ain't strings until you make them strings... If Len(x(m)) = 1 Then x(m) = "000" & x(m) If Len(x(m)) = 1 Then x(m) = "00" & x(m) If Len(x(m)) = 1 Then x(m) = "0" & x(m) -- Jim Cone Portland, Oregon USA ( http://www.mediafire.com/PrimitiveSoftware ) "Philosophaie" <Philosophaie(a)discussions.microsoft.com> wrote in message news:9FA3AEB6-FCD8-4D78-A50C-992479F9EF5F(a)microsoft.com... I want to have a 4 digit number in all cases but some have 0 thru 3. This is how I attempted to solve it: dim x(3) as string x(1)=234 x(2)=67 x(3)=5 If Len(x(m)) = 1 Then x(m) = 000 & x(m) If Len(x(m)) = 1 Then x(m) = 00 & x(m) If Len(x(m)) = 1 Then x(m) = 0 & x(m) the problem is when you enter "000" in for the result it automatically reverts back to "0"
From: Rick Rothstein on 22 May 2010 00:00
Don't use a series of If..Then tests (by the way, the numbers you are testing against should be 1, 2, 3 and not 1, 1, 1), use the Format function instead. For all your cases, use this single statement... x(m) = Format(x(m), "0000") -- Rick (MVP - Excel) "Philosophaie" <Philosophaie(a)discussions.microsoft.com> wrote in message news:9FA3AEB6-FCD8-4D78-A50C-992479F9EF5F(a)microsoft.com... > I want to have a 4 digit number in all cases but some have 0 thru 3. > > This is how I attempted to solve it: > > dim x(3) as string > x(1)=234 > x(2)=67 > x(3)=5 > If Len(x(m)) = 1 Then x(m) = 000 & x(m) > If Len(x(m)) = 1 Then x(m) = 00 & x(m) > If Len(x(m)) = 1 Then x(m) = 0 & x(m) > the problem is when you enter "000" in for the result it automatically > reverts back to "0" |