A visual basic value copy BUG?? - accounting format has copy problem!!

[yunyanl]

I just got an unbelievable behavior from excel when i did the following
value copying:
-----------------------------
Sub mycopy()
Range("A2:B2").Value = Range("A1:B1").Value
End Sub
-----------------------------

I formatted cell A1 as accounting format (or currency format $) and
input $0.0123 in A1.
format cell B1 as general format and input 0.0123 into B1.

After running the macro, you'll see the target values of A2 becomes
$0.0100, B2 is 0.0123. (i'm using excel 2003)

THe accounting/currency format just use the rounding automatically!!!
Is this a bug? or a feature?

BTW, i tried after to use ".copy" and ".PasteSpecial xlPasteValues",
that works regardless. But not knowing the limit of range copy made
million dollar difference in my analysis! DARN it!

 

[witek]

I just got an unbelievable behavior from excel when i did the following
value copying:
-----------------------------
Sub mycopy()
Range("A2:B2").Value = Range("A1:B1").Value
End Sub
-----------------------------

I formatted cell A1 as accounting format (or currency format $) and
input $0.0123 in A1.
format cell B1 as general format and input 0.0123 into B1.

After running the macro, you'll see the target values of A2 becomes
$0.0100, B2 is 0.0123. (i'm using excel 2003)

THe accounting/currency format just use the rounding automatically!!!
Is this a bug? or a feature?

BTW, i tried after to use ".copy" and ".PasteSpecial xlPasteValues",
that works regardless. But not knowing the limit of range copy made
million dollar difference in my analysis! DARN it!

I have the same. Definitely it's a bug.

 

[Peter T]

This relates to Excel's automatic conversion of dates & currencies, which
gets it right for users 'most' of the time.

dim cur as currency
cur = 0.0123
[c4]=cur ' 0.01
[c5] = cdbl(cur) ' 0.0123

back to your issue, you'll note when you did -
Range("A2:B2").Value = Range("A1:B1").Value

that A2 is automatically formatted as currency, because the data type
(VarType) of that element in the array is currency.

You could do -
Range("A2:B2").Value = Range("A1:B1").Value2

which will place the intrinsic values but won't change the existing format.

Regards,
Peter T

 

[yunyanl]

Wow Peter~! Thanks a lot! That ".value2" works amazingly fine!!
Thanks a million for the tip.

This relates to Excel's automatic conversion of dates & currencies, which
gets it right for users 'most' of the time.

dim cur as currency
cur = 0.0123
[c4]=cur ' 0.01
[c5] = cdbl(cur) ' 0.0123

back to your issue, you'll note when you did -
Range("A2:B2").Value = Range("A1:B1").Value

that A2 is automatically formatted as currency, because the data type
(VarType) of that element in the array is currency.

You could do -
Range("A2:B2").Value = Range("A1:B1").Value2

which will place the intrinsic values but won't change the existing format.

Regards,
Peter T

I just got an unbelievable behavior from excel when i did the following
value copying:
-----------------------------
Sub mycopy()
Range("A2:B2").Value = Range("A1:B1").Value
End Sub
-----------------------------

I formatted cell A1 as accounting format (or currency format $) and
input $0.0123 in A1.
format cell B1 as general format and input 0.0123 into B1.

After running the macro, you'll see the target values of A2 becomes
$0.0100, B2 is 0.0123. (i'm using excel 2003)

THe accounting/currency format just use the rounding automatically!!!
Is this a bug? or a feature?

BTW, i tried after to use ".copy" and ".PasteSpecial xlPasteValues",
that works regardless. But not knowing the limit of range copy made
million dollar difference in my analysis! DARN it!