Thread: Copy cell as non-scientific number VBA

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)

            .Cells(NextRecord2, 9).Value = "Cntrl#"
            .Cells(NextRecord2, 10).Value = Range(FieldOrder(23) & i).Value

The entire code is:

Sub TradeAnalysis()

Dim i               As Long
Dim j               As Long
Dim n               As Long
Dim LastRow         As Long
Dim Prompt          As String
Dim Title           As String
Dim Path            As String
Dim FieldOrder      As New Collection
Dim WkbData         As Workbook

Call ClearContents

    FieldOrder.Add "E"   '1 - Status
    FieldOrder.Add "W"   '2 - Execution Date
    FieldOrder.Add "X"   '3 - Execution Time
    FieldOrder.Add "B"   '4 - Reporting Date
    FieldOrder.Add "C"   '5 - Reporting Time
    FieldOrder.Add "H"   '6 - Symbol
    FieldOrder.Add "I"   '7 - Cusip
    FieldOrder.Add "G"   '8 - Side
    FieldOrder.Add "O"   '9 - Reporting Party Capacity
    FieldOrder.Add "J"   '10 - Quantity
    FieldOrder.Add "K"   '11 - Price
    FieldOrder.Add "AF"  '12 - Commission
    FieldOrder.Add "AV"  '13 - Yield
    FieldOrder.Add "D"   '14 - Security Type
    FieldOrder.Add "AC"  '15 - Trade Market Indicator
    FieldOrder.Add "M"   '16 - Reporting Party (RPID)
    FieldOrder.Add "N"   '17 - Reporting Party Give Up (RPGU)
    FieldOrder.Add "Q"   '18 - Contra Party (RPID)
    FieldOrder.Add "R"   '19 - Contra Party Give Up (RPGU)
    FieldOrder.Add "F"   '20 - Reversal Indictor
    FieldOrder.Add "AG"  '21 - Branch Sequence Number
    FieldOrder.Add "AR"  '22 - Client Trade ID (Reference Number)
    FieldOrder.Add "AO"  '23 - TRACE Control Number
    FieldOrder.Add "Y"   '24 - Trade Mod 1
    FieldOrder.Add "Z"   '25 - Trade Mod 2

    n = FieldOrder.Count
    
    Prompt = "Select the file to process."
    Path = Application.GetOpenFilename("Text Files (*.csv), *.csv", , Prompt)
    If Path = "False" Then
        GoTo ExitSub:
    End If

    Workbooks.OpenText Filename:=Path, Origin:=2, StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:=","  ', TrailingMinusNumbers:=True
    Set WkbData = ActiveWorkbook
    
    With wsCorporateAgency
        .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).ClearContents
        LastRow = Range("B" & .Rows.Count).End(xlUp).Row
        NextRecord1 = 3
        NextRecord2 = 4
        
        For i = 2 To LastRow

            For j = 1 To 20
                Select Case j
                    Case 1
                        .Cells(NextRecord1, j).Value = Left(Range(FieldOrder(j) & i).Value, 1)
                    Case 8
                        .Cells(NextRecord1, j).Value = Left(Range(FieldOrder(j) & i).Value, 1)
                    Case 9
                        .Cells(NextRecord1, j).Value = Left(Range(FieldOrder(j) & i).Value, 1)
                    Case 12
                        If Range(FieldOrder(j) & i).Value = "" Then
                            .Cells(NextRecord1, j).Value = 0
                        Else
                            .Cells(NextRecord1, j).Value = Range(FieldOrder(j) & i).Value
                        End If
                    Case Else
                        .Cells(NextRecord1, j).Value = Range(FieldOrder(j) & i).Value
                End Select
            Next j
            
            .Cells(NextRecord2, 2).Value = "BR Seq#"
            .Cells(NextRecord2, 3).Value = Range(FieldOrder(21) & i).Value

            .Cells(NextRecord2, 5).Value = "Ref #"
            .Cells(NextRecord2, 6).Value = Range(FieldOrder(22) & i).Value

            .Cells(NextRecord2, 9).Value = "Cntrl#"
            .Cells(NextRecord2, 10).Value = Range(FieldOrder(23) & i).Value

            .Cells(NextRecord2, 13).Value = "Mod1"
            .Cells(NextRecord2, 14).Value = Range(FieldOrder(24) & i).Value
            
            .Cells(NextRecord2, 15).Value = "Mod2"
            .Cells(NextRecord2, 16).Value = Range(FieldOrder(25) & i).Value

            NextRecord1 = NextRecord1 + 3
            NextRecord2 = NextRecord2 + 3
            
        Next i
        WkbData.Close SaveChanges:=False
    End With

ExitSub:
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
    
    Set WkbData = Nothing
    
End Sub

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

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:

            .Cells(NextRecord2, 5).Value = "Ref #"
            .Cells(NextRecord2, 6).Value = "'" & Range(FieldOrder(22) & i).Value

To test:

Cells(10, 5).Value = "Ref #"
Cells(10, 6).Value = "'" & "2320000000000000000"

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


Regards, TMS