Why does cell format change when assigning a value?

[N Selinger]

I have some VBA code which accumulates a total of several cells, then puts
that total into a cell formatted as "Accounting" format. Every time this
code runs, it changes the cell formatting to "Currency".

I have tested this with a new, blank workbook and I get the same result.
Try the following:

Sub test()
a = 10
Cells(2, 1) = 0
a = a + Cells(2, 1)
Cells(3, 1).Value = a
End Sub

Why does it change the formatting??? And how do get around this problem?

 

[JoeU2004]

I have some VBA code which accumulates a total of several cells, then
puts that total into a cell formatted as "Accounting" format. Every time
this code runs, it changes the cell formatting to "Currency".
[....]
And how do get around this problem?

Try the following test code:

Range("A2").Value2 = Range("A2") + 1

Note the use of Value2 instead of Value.

Why does it change the formatting???

In Unix, we have a saying: "don't ask why" ;-). But the following might
provide a hint:
http://msdn.microsoft.com/en-us/library/aa215938(office.11).aspx .


----- original message -----

 

[JoeU2004]

Let's try this again, this time with proper explanation....

I have some VBA code which accumulates a total of several cells, then
puts that total into a cell formatted as "Accounting" format. Every time
this code runs, it changes the cell formatting to "Currency".
[....]
And how do get around this problem?

Put a value into A2 and format A2 as Accounting.

Then try the following test code:

Range("A2").Value2 = Range("A2") + 1

Note the use of Value2 instead of Value.

Why does it change the formatting???

In Unix, we have a saying: "don't ask why" ;-). But the following might
provide a hint:
http://msdn.microsoft.com/en-us/library/aa215938(office.11).aspx .


----- original message -----