Number Format

[Alec Kolundzic]

Can anyone help.

The following code is part of a number generator, that
fills a column with numbers in the format "0000" ie on 5
instances of AI, numbers 0700 - 0704 will be generated.
However, when I try to add "+" or "-" to the end of the
number, the numbers generated are 0700+, 701+, 702+, 703+,
704+. After the first instance I loose the leading "0".

Any ideas on how I can maintain my number format, and also
add "+" or "-" to the end of it.

Thanks
Alec


Case "AI"
.Offset(0, 4).Value = counter(4 * Z - 2, 0) & "+"
If counter(4 * Z - 2, 1) = B Then
counter(4 * Z - 2, 1) = 0
counter(4 * Z - 2, 0) = counter(4 * Z - 2, 0) + 100 - B
Else
counter(4 * Z - 2, 1) = counter(4 * Z - 2, 1) + 1
counter(4 * Z - 2, 0) = counter(4 * Z - 2, 0) + 1
End If

 

[Ron Rosenfeld]

Can anyone help.

The following code is part of a number generator, that
fills a column with numbers in the format "0000" ie on 5
instances of AI, numbers 0700 - 0704 will be generated.
However, when I try to add "+" or "-" to the end of the
number, the numbers generated are 0700+, 701+, 702+, 703+,
704+. After the first instance I loose the leading "0".

Any ideas on how I can maintain my number format, and also
add "+" or "-" to the end of it.

Thanks
Alec


Case "AI"
.Offset(0, 4).Value = counter(4 * Z - 2, 0) & "+"
If counter(4 * Z - 2, 1) = B Then
counter(4 * Z - 2, 1) = 0
counter(4 * Z - 2, 0) = counter(4 * Z - 2, 0) + 100 - B
Else
counter(4 * Z - 2, 1) = counter(4 * Z - 2, 1) + 1
counter(4 * Z - 2, 0) = counter(4 * Z - 2, 0) + 1
End If

It depends on whether you want the result to be a number, or if the result can
be text.

If the result must be a number, then you need to change the cell format.

If your code snipped is from a UDF, (Function), then you'll need to set the
number format of the cell separately, according to whatever your rules are for
having a trailing '+' or trailing '-', as I don't believe a Function can do
that.

One method of formatting would be the custom format: 0000+;0000-;- would
result in a trailing sign and maintain the leading zero's.

If the above is a sub, then instead of the &"+" you would do something like

.Offset(0, 4).Value = counter(4 * Z - 2, 0)
.Offset(0,4).NumberFormat = 0000+;0000+

If the above is a Function, then you could output a Text string:

.Offset(0, 4).Text = Format(counter(4 * Z - 2, 0),"0000+;0000+")

Hope these ideas help.



--ron

 

[Alec Kolundzic]

Thanks Ron,

You are right, individually formatting each cell through
the loop results in what I'm trying to achieve.

 

[Ron Rosenfeld]

Thanks Ron,

You are right, individually formatting each cell through
the loop results in what I'm trying to achieve.

You're welcome. Glad I could help.


--ron