Number formatting in arrays

[tiredofit]

In Excel, I have 3 numbers in cells A1 through to A3.

These are:

1, 2 and 12345678901234567890

If column A was formatted as a number, it will show as:

1, 2 and 12345678901234500000

The reason is Excel truncates long numbers.

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

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim MyArray() As Variant
    
    MyArray() = Cells(1, 1).CurrentRegion.Value

I see MyArray(3) shows:

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]

I think you need to explicitly format the cell as numeric .... it looks like you have it formatted as General

 

[tiredofit]

Thanks