• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Prevent macro from rounding numbers

S

Syerram

New Member
Hi,


I am using a simple one line macro to copy value from one range to another. Problem is that the macro seems to be rounding the numbers to 2 decimal places, while I want no rounding.


The code i am using is:


Sub Sens1()


Worksheets("Dashboard").Range("I26:I38").Value = Worksheets("Dashboard").Range("I6:I18").Value


End Sub


Is there something I am doing wrong? This may have a terribly simple solution, but I am truly lost!


Many thanks for responses :)


Yerram
 
Check your cell formatting in I26:I38. The macro is working correctly and transferring the complete cell value. Perhaps I26:I38 are simply formatted to only show 2 decimals?
 
Thanks Luke.


The cell from where it was being copies was formatted as 'Currency'. When I changed the formatting to General, this error disappeared!


Also, is there a way for the macro to be dynamic - example, in my workbook, I move some information around and hence the cells from wher i have to copy values changes. So everytime i change the layout of the sheet, I have to revise the cell references in the macro. Is there a way to avoid this please?


As always, you are a star and thanks a ton!
 
Strange. If your code has use .Text (which is just what is displayed) instead of .Value (which is the actual value), I would have expected that to be an issue, but I'm glad that overall you were able to solve it.


Concerning the moving around of data, it somewhat depends. If the movement is caused by cells being inserted/deleted, I'd recommend using a named range. Simply select the cells of interest and give them some name (ie, MyRange and DestRange). Then, your code line becomes:

[pre]
Code: 
Worksheets("Dashboard").Range("MyRange").Value = Worksheets("Dashboard").Range("DestRange").Value
[/pre]
If the info is moving around for other reasons, we may have to do some work to find it. This could involve using the .Find method, or perhaps using the .Resize method if the number of cells to be copied is changing. If the issue is not being caused by one of these, perhaps you could elaborate on why the data is moving around?


You're welcome, and thanks for the kind words. =)
 
Thanks Luke. The info will only move around with inserting / deleting cells. So I named my origin and destination cells as 'Copy' and 'Paste', and it seems to have done the trick.


thanks a ton again!
 
You must log in or register to reply here.
Back
Top