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

[yunyanl@gmail.com]

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]

yunyanl@gmail.com wrote:
> 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@gmail.com> wrote in message
news:1150751935.883946.144010@p79g2000cwp.googlegroups.com...
> 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!
>

[yunyanl@gmail.com]

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

Peter T wrote:
> 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@gmail.com> wrote in message
> news:1150751935.883946.144010@p79g2000cwp.googlegroups.com...
> > 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!
> >