Copy cell as non-scientific number VBA

[maacmaac]

I am copying a cell from another workbook using VBA. It is importing the number with scientific notation. I want to copy the cell without the scientific notation. I have tried to format the cells in the destination worksheet prior to import and other methods but having no luck. The line I am using to copy is: (I have attached a sample spreadsheet if needed; I can't upload the data file as it is in a .csv format)

Please Login or Register  to view this content.

The entire code is:

Please Login or Register  to view this content.

[shg]

Format the cells of interest as text beforehand (or in your code).

[TMS]

In cell F4 you have 2320000000000000000 which is displayed as 2.32E+18

The reason for that is the size of that number ... all those zeroes. Excel can only cope with 15 significant digits and here you have 20, hence the scientific notation.

You might get away with:

Please Login or Register  to view this content.

To test:

Please Login or Register  to view this content.

Note that if I don't put the 2320000000000000000 in quotes, VBA converts it to scientific notation.


Regards, TMS

[maacmaac]

I tried following, which works

Please Login or Register  to view this content.

I also tried following, which did not work

Please Login or Register  to view this content.

I also tried to format the cells of interest first as text. That didn't work either. I have attached a copy of the source data (I converted from .csv to .xls in order to attach). The problem column is "AR". Thanks for the comments.

[maacmaac]

I was finally able to figure out a workaround. I had to format the source file first as suggested by SHG. It probably isn't the most efficient code, but it solves what I am trying to do. Thanks for all the comments.

Please Login or Register  to view this content.