Hi Experts!
I'm confused by the way ActiveCell.Value works. Here is my problem:

I try to pull the value of a cell from a different worksheet. This
'source-cell' contains the value e.g. "6.6513" formatted as currency with two
decimal places "$6.65".

Source Sheet/Cell:
TestValue = ActiveCell.Value
Debug.Print TestValue (=shows 6.6513)

Destination Sheet/Cell:
Range("I17").Value = TestValue

The value shown in destination cell I17 now shows "$6.65" and its absolute
value is "6.65" the last two decimal places were not put in I17. The same
example with the source-cell being formatted as number with two decimal
places (instead of currency) shows also "6.65" in I17 but its absolute is
"6.6513"????

Does anyone have an explanation for me? I cannot see any logical reason
behind it...

Thank you,
Bluda

Try declaring TestValue as a double. I don't know the details, but has
something to do w/Testvalue being a variant (currency subtype) which is the
default data type when you don't declare them.



"Bluda" wrote:

> Hi Experts!
> I'm confused by the way ActiveCell.Value works. Here is my problem:
>
> I try to pull the value of a cell from a different worksheet. This
> 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> decimal places "$6.65".
>
> Source Sheet/Cell:
> TestValue = ActiveCell.Value
> Debug.Print TestValue (=shows 6.6513)
>
> Destination Sheet/Cell:
> Range("I17").Value = TestValue
>
> The value shown in destination cell I17 now shows "$6.65" and its absolute
> value is "6.65" the last two decimal places were not put in I17. The same
> example with the source-cell being formatted as number with two decimal
> places (instead of currency) shows also "6.65" in I17 but its absolute is
> "6.6513"????
>
> Does anyone have an explanation for me? I cannot see any logical reason
> behind it...
>
> Thank you,
> Bluda

Thanks JMB.

Sorry for not mentioning this information before but the Variable is
declared as a 'Double'.
I have looked through my entire code and verified that it's not a code
'misinterpretation'. I used the code sample in a different workbook and just
to make sure but I have received the same results. I have absolutely no clue
why this is happening.



"JMB" wrote:

> Try declaring TestValue as a double. I don't know the details, but has
> something to do w/Testvalue being a variant (currency subtype) which is the
> default data type when you don't declare them.
>
>
>
> "Bluda" wrote:
>
> > Hi Experts!
> > I'm confused by the way ActiveCell.Value works. Here is my problem:
> >
> > I try to pull the value of a cell from a different worksheet. This
> > 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> > decimal places "$6.65".
> >
> > Source Sheet/Cell:
> > TestValue = ActiveCell.Value
> > Debug.Print TestValue (=shows 6.6513)
> >
> > Destination Sheet/Cell:
> > Range("I17").Value = TestValue
> >
> > The value shown in destination cell I17 now shows "$6.65" and its absolute
> > value is "6.65" the last two decimal places were not put in I17. The same
> > example with the source-cell being formatted as number with two decimal
> > places (instead of currency) shows also "6.65" in I17 but its absolute is
> > "6.6513"????
> >
> > Does anyone have an explanation for me? I cannot see any logical reason
> > behind it...
> >
> > Thank you,
> > Bluda

I cannot duplicate your problem then. When declared as variant, I get your
results. When declared double I get 6.6513 in cell I7 (although only 2
decimals may be displayed). Only other thing I can suggest is to double
check the spelling of your variable. for example:

Dim MyVariable as Double

MyVaraible = Activecell
Range("I7") = MyVaraible

If Option Explicit is not at the top of your module, XL will happily create
MyVaraible (and it will be a variant).


"Bluda" wrote:

> Thanks JMB.
>
> Sorry for not mentioning this information before but the Variable is
> declared as a 'Double'.
> I have looked through my entire code and verified that it's not a code
> 'misinterpretation'. I used the code sample in a different workbook and just
> to make sure but I have received the same results. I have absolutely no clue
> why this is happening.
>
>
>
> "JMB" wrote:
>
> > Try declaring TestValue as a double. I don't know the details, but has
> > something to do w/Testvalue being a variant (currency subtype) which is the
> > default data type when you don't declare them.
> >
> >
> >
> > "Bluda" wrote:
> >
> > > Hi Experts!
> > > I'm confused by the way ActiveCell.Value works. Here is my problem:
> > >
> > > I try to pull the value of a cell from a different worksheet. This
> > > 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> > > decimal places "$6.65".
> > >
> > > Source Sheet/Cell:
> > > TestValue = ActiveCell.Value
> > > Debug.Print TestValue (=shows 6.6513)
> > >
> > > Destination Sheet/Cell:
> > > Range("I17").Value = TestValue
> > >
> > > The value shown in destination cell I17 now shows "$6.65" and its absolute
> > > value is "6.65" the last two decimal places were not put in I17. The same
> > > example with the source-cell being formatted as number with two decimal
> > > places (instead of currency) shows also "6.65" in I17 but its absolute is
> > > "6.6513"????
> > >
> > > Does anyone have an explanation for me? I cannot see any logical reason
> > > behind it...
> > >
> > > Thank you,
> > > Bluda

When you use VBA to make a cell equal the value of another cell, that is what
you get, the value. You had the source cell formatted to two decimal points
and that is the value of the cell, although the underlying value is 12 one
hundreths greater, only the two decimal point value will be transferred to
another cell if you use:
Range(x) = Range(y).Value.

"Bluda" wrote:

> Hi Experts!
> I'm confused by the way ActiveCell.Value works. Here is my problem:
>
> I try to pull the value of a cell from a different worksheet. This
> 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> decimal places "$6.65".
>
> Source Sheet/Cell:
> TestValue = ActiveCell.Value
> Debug.Print TestValue (=shows 6.6513)
>
> Destination Sheet/Cell:
> Range("I17").Value = TestValue
>
> The value shown in destination cell I17 now shows "$6.65" and its absolute
> value is "6.65" the last two decimal places were not put in I17. The same
> example with the source-cell being formatted as number with two decimal
> places (instead of currency) shows also "6.65" in I17 but its absolute is
> "6.6513"????
>
> Does anyone have an explanation for me? I cannot see any logical reason
> behind it...
>
> Thank you,
> Bluda

Thanks JLGWhiz.

I'm not sure I fully understand your comments. If the value is e.g. "9.1234"
then no matter how you format this number the value "9.1234" should never
change to "9.12".

I just noted the following, I declared my Variables in VBA the way I was
used to declare my Variables in Delphi:
Dim TestValue1, TestValue2 As Double
Apparently the first Variable was not declared as a double? Is that possible?
When changed the variables declaration to:
Dim TestValue1 As Double
Dim TestValue2 As Double
Declaring each variable individually worked now in my example. Could that
have something to do with it???




"JLGWhiz" wrote:

> When you use VBA to make a cell equal the value of another cell, that is what
> you get, the value. You had the source cell formatted to two decimal points
> and that is the value of the cell, although the underlying value is 12 one
> hundreths greater, only the two decimal point value will be transferred to
> another cell if you use:
> Range(x) = Range(y).Value.
>
> "Bluda" wrote:
>
> > Hi Experts!
> > I'm confused by the way ActiveCell.Value works. Here is my problem:
> >
> > I try to pull the value of a cell from a different worksheet. This
> > 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> > decimal places "$6.65".
> >
> > Source Sheet/Cell:
> > TestValue = ActiveCell.Value
> > Debug.Print TestValue (=shows 6.6513)
> >
> > Destination Sheet/Cell:
> > Range("I17").Value = TestValue
> >
> > The value shown in destination cell I17 now shows "$6.65" and its absolute
> > value is "6.65" the last two decimal places were not put in I17. The same
> > example with the source-cell being formatted as number with two decimal
> > places (instead of currency) shows also "6.65" in I17 but its absolute is
> > "6.6513"????
> >
> > Does anyone have an explanation for me? I cannot see any logical reason
> > behind it...
> >
> > Thank you,
> > Bluda

Thanks JMB.

I just noticed something in my declaration of the variables but I'm not sure
if that was/is the reason. Please see my reply to JLGWhiz.


"JMB" wrote:

> I cannot duplicate your problem then. When declared as variant, I get your
> results. When declared double I get 6.6513 in cell I7 (although only 2
> decimals may be displayed). Only other thing I can suggest is to double
> check the spelling of your variable. for example:
>
> Dim MyVariable as Double
>
> MyVaraible = Activecell
> Range("I7") = MyVaraible
>
> If Option Explicit is not at the top of your module, XL will happily create
> MyVaraible (and it will be a variant).
>
>
> "Bluda" wrote:
>
> > Thanks JMB.
> >
> > Sorry for not mentioning this information before but the Variable is
> > declared as a 'Double'.
> > I have looked through my entire code and verified that it's not a code
> > 'misinterpretation'. I used the code sample in a different workbook and just
> > to make sure but I have received the same results. I have absolutely no clue
> > why this is happening.
> >
> >
> >
> > "JMB" wrote:
> >
> > > Try declaring TestValue as a double. I don't know the details, but has
> > > something to do w/Testvalue being a variant (currency subtype) which is the
> > > default data type when you don't declare them.
> > >
> > >
> > >
> > > "Bluda" wrote:
> > >
> > > > Hi Experts!
> > > > I'm confused by the way ActiveCell.Value works. Here is my problem:
> > > >
> > > > I try to pull the value of a cell from a different worksheet. This
> > > > 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> > > > decimal places "$6.65".
> > > >
> > > > Source Sheet/Cell:
> > > > TestValue = ActiveCell.Value
> > > > Debug.Print TestValue (=shows 6.6513)
> > > >
> > > > Destination Sheet/Cell:
> > > > Range("I17").Value = TestValue
> > > >
> > > > The value shown in destination cell I17 now shows "$6.65" and its absolute
> > > > value is "6.65" the last two decimal places were not put in I17. The same
> > > > example with the source-cell being formatted as number with two decimal
> > > > places (instead of currency) shows also "6.65" in I17 but its absolute is
> > > > "6.6513"????
> > > >
> > > > Does anyone have an explanation for me? I cannot see any logical reason
> > > > behind it...
> > > >
> > > > Thank you,
> > > > Bluda

> Dim TestValue1, TestValue2 As Double

Testvalue1 is a variant.

JLGWhiz is right when you use Range(x) = Range(y).Value, but I get all of
the decimal places when the value is stored in a variable (if it is declared
as double and not variant) then assigned to another cell, regardless of the
formatting.


"Bluda" wrote:

> Thanks JLGWhiz.
>
> I'm not sure I fully understand your comments. If the value is e.g. "9.1234"
> then no matter how you format this number the value "9.1234" should never
> change to "9.12".
>
> I just noted the following, I declared my Variables in VBA the way I was
> used to declare my Variables in Delphi:
> Dim TestValue1, TestValue2 As Double
> Apparently the first Variable was not declared as a double? Is that possible?
> When changed the variables declaration to:
> Dim TestValue1 As Double
> Dim TestValue2 As Double
> Declaring each variable individually worked now in my example. Could that
> have something to do with it???
>
>
>
>
> "JLGWhiz" wrote:
>
> > When you use VBA to make a cell equal the value of another cell, that is what
> > you get, the value. You had the source cell formatted to two decimal points
> > and that is the value of the cell, although the underlying value is 12 one
> > hundreths greater, only the two decimal point value will be transferred to
> > another cell if you use:
> > Range(x) = Range(y).Value.
> >
> > "Bluda" wrote:
> >
> > > Hi Experts!
> > > I'm confused by the way ActiveCell.Value works. Here is my problem:
> > >
> > > I try to pull the value of a cell from a different worksheet. This
> > > 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> > > decimal places "$6.65".
> > >
> > > Source Sheet/Cell:
> > > TestValue = ActiveCell.Value
> > > Debug.Print TestValue (=shows 6.6513)
> > >
> > > Destination Sheet/Cell:
> > > Range("I17").Value = TestValue
> > >
> > > The value shown in destination cell I17 now shows "$6.65" and its absolute
> > > value is "6.65" the last two decimal places were not put in I17. The same
> > > example with the source-cell being formatted as number with two decimal
> > > places (instead of currency) shows also "6.65" in I17 but its absolute is
> > > "6.6513"????
> > >
> > > Does anyone have an explanation for me? I cannot see any logical reason
> > > behind it...
> > >
> > > Thank you,
> > > Bluda

BTW, to declare both on one line, try:
Dim TestValue1 As Double, TestValue2 As Double

"Bluda" wrote:

> Thanks JLGWhiz.
>
> I'm not sure I fully understand your comments. If the value is e.g. "9.1234"
> then no matter how you format this number the value "9.1234" should never
> change to "9.12".
>
> I just noted the following, I declared my Variables in VBA the way I was
> used to declare my Variables in Delphi:
> Dim TestValue1, TestValue2 As Double
> Apparently the first Variable was not declared as a double? Is that possible?
> When changed the variables declaration to:
> Dim TestValue1 As Double
> Dim TestValue2 As Double
> Declaring each variable individually worked now in my example. Could that
> have something to do with it???
>
>
>
>
> "JLGWhiz" wrote:
>
> > When you use VBA to make a cell equal the value of another cell, that is what
> > you get, the value. You had the source cell formatted to two decimal points
> > and that is the value of the cell, although the underlying value is 12 one
> > hundreths greater, only the two decimal point value will be transferred to
> > another cell if you use:
> > Range(x) = Range(y).Value.
> >
> > "Bluda" wrote:
> >
> > > Hi Experts!
> > > I'm confused by the way ActiveCell.Value works. Here is my problem:
> > >
> > > I try to pull the value of a cell from a different worksheet. This
> > > 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> > > decimal places "$6.65".
> > >
> > > Source Sheet/Cell:
> > > TestValue = ActiveCell.Value
> > > Debug.Print TestValue (=shows 6.6513)
> > >
> > > Destination Sheet/Cell:
> > > Range("I17").Value = TestValue
> > >
> > > The value shown in destination cell I17 now shows "$6.65" and its absolute
> > > value is "6.65" the last two decimal places were not put in I17. The same
> > > example with the source-cell being formatted as number with two decimal
> > > places (instead of currency) shows also "6.65" in I17 but its absolute is
> > > "6.6513"????
> > >
> > > Does anyone have an explanation for me? I cannot see any logical reason
> > > behind it...
> > >
> > > Thank you,
> > > Bluda

Try:

Dim TestValue as Double
Testvalue = activecell.value2

Check out VBA's help for .Value2. It's particularly useful for dates and
currency.

Bluda wrote:
>
> Hi Experts!
> I'm confused by the way ActiveCell.Value works. Here is my problem:
>
> I try to pull the value of a cell from a different worksheet. This
> 'source-cell' contains the value e.g. "6.6513" formatted as currency with two
> decimal places "$6.65".
>
> Source Sheet/Cell:
> TestValue = ActiveCell.Value
> Debug.Print TestValue (=shows 6.6513)
>
> Destination Sheet/Cell:
> Range("I17").Value = TestValue
>
> The value shown in destination cell I17 now shows "$6.65" and its absolute
> value is "6.65" the last two decimal places were not put in I17. The same
> example with the source-cell being formatted as number with two decimal
> places (instead of currency) shows also "6.65" in I17 but its absolute is
> "6.6513"????
>
> Does anyone have an explanation for me? I cannot see any logical reason
> behind it...
>
> Thank you,
> Bluda

--

Dave Peterson