Number formatting in arrays

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,102
In Excel, I have 3 numbers in cells A1 through to A3.

These are:

Rich (BB code):
1, 2 and 12345678901234567890
If column A was formatted as a number, it will show as:

Rich (BB code):
Rich (BB code):
Rich (BB code):
1, 2 and 12345678901234500000

The reason is Excel truncates long numbers.

When I look at the Locals Windows when running this code:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim MyArray() As Variant
    
    MyArray() = Cells(1, 1).CurrentRegion.Value
I see MyArray(3) shows:

Rich (BB code):
1.23456789012345E+19
Is there a way to force the Locals Window to display the actual figure (without the E)?

I don't mind if it displays the true figure of 12345678901234567890 or the truncated figure of 12345678901234500000.

The reason for all this is I have long numbers which are subsequently used in a lookup and therefore I cannot have the numbers truncated.

To overcome this, I add an apostrophe in front but if the number is displayed with an E, then adding an apostrophe at the beginning will turn it into text.

Thanks


[/FONT]
 

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
I think you need to explicitly format the cell as numeric .... it looks like you have it formatted as General
 

Forum statistics

Threads
1,085,014
Messages
5,381,232
Members
401,721
Latest member
karloqs

Some videos you may like

This Week's Hot Topics

Top