Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lose precision when copy between cells (2000/2002)

    In a workbook I've created I have a feature (button) which restores the current sheet to a set of default values. These default values are stored on a hidden (very hidden) sheet called Admin. When the user clicks the [Use Default Data] button the code copies the data from the appropriate section on the Admin sheet to the same section on the current sheet. I used named ranges to do this and it all works great.

    My problem? I originally copied the formula from the Admin sheet (source data) to the current sheet (destination sheet). I then decided I should just copy the actual data from the Admin sheet but now I see that in some cases I lose precision.

    Example: Admin data cell holds 0.042 but when I copy this data to the appropriate cell in the current sheet, it shows up as 0.040. If I copy the source formula to the destination cell, I get the 'real' data, 0.042. Why is that?

    Here's the code which copies the formula from an area on the Admin sheet to the active sheet. The commented out code is what I was doing to copy the data only but that's what gives me the wrong precision.
    <pre> For rw = 1 To rngSrc.Areas(3).Cells.Rows.Count
    rngDest.Areas(3).Cells(rw, 1).Formula = "=" & rngSrc.Parent.Name & "!" & _
    rngSrc.Areas(3).Cells(rw, 1).Address(False, False)
    ' rngDest.Areas(3).Cells(rw, 1).Value = rngSrc.Areas(3).Cells(rw, 1).Value
    Next rw</pre>

    'rngDest' = destination range (active sheet), and 'rngSrc' = source range on the Admin sheet.

    I prefer to just copy the data and not have the user see the formula in the formula bar if they click on these cells (although they are protected).

    Any idea why copying data via value vs. via formula results in a different number? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thnx, Deb

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lose precision when copy between cells (2000/2002)

    You haven't got "Precision as displayed" set, have you?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lose precision when copy between cells (2000/2002)

    No I don't have that set. I had to finish this project by last night so I just used the code that copies the formula over and not the data itself. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> The two cells were both formatted as Currency with 3 decimal places.

    I just now opened a new workbook in Excel2002 and wrote 4 lines of code to do this data copy from one sheet to another as well as from two different cells on the same sheet. It worked fine (of course). <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    At least I was able to solve the problem but don't know what caused it. I'll have to be careful about this in the future as I use this 'restore default data' feature a lot in the stuff I write.

    Thnx, Deb

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Lose precision when copy between cells (2000/2002)

    Deb,

    Using "Value2" instead of "Value" should solve the precision problem.
    MS kind of made a botch of the value property.
    Its a good idea to use Value2 anytime there are numbers with more than two decimals.

    Regards,

    Jim Cone
    San Francisco, CA

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lose precision when copy between cells (2000/2002)

    Hey, that actually worked! I also tried it w/o .Value and that too worked. I'll add this to my growing list of Excel oddities. (Did you know that in Excel 97 if you try to protect a sheet that's already protected it generates an error? It's ok in 2000/2002. Also in Excel 2002 if you protect a workbook (with VBA) that's already protected, the workbook gets unprotected! <img src=/S/bash.gif border=0 alt=bash width=35 height=39> Just more things I've stumbled on - so much for version compatibility. This sort of thing drives me nuts <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> . I work in all 3 Excel versions and keeping it all straight significantly adds to the development time.

    Thnx again, Deb

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lose precision when copy between cells (2000/2002)

    Yeah great experience. I have the same problem with our Name Manager: 75 % of the work I've been doing on it lately is about working around a few annoying bugs in Excel's Names object.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •