Consulting

Results 1 to 6 of 6

Thread: Changing numbers with scientific notation (E+) in pivot table created by VBA

  1. #1

    Question Changing numbers with scientific notation (E+) in pivot table created by VBA

    My pivot table created by a VBA Macro is not displaying the account numbers in full. It summaries them with a scientific notation.

    What VBA code can I use in the macro to force it to fully display the number irrespective of which PC it's run? Code of Macro & sample data is shown in the tabs of the attached file.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    When I run a corrected version (your code tries to create 2 pivot tables) of the macro I get the full 13 digits of the account numbers showing in the pivot. So perhaps a quick fix is not to try and change the formatting at all!
    As soon as I try to adjust the formatting (to text) it then converts to scientific notation - which to me, is perverse.

    I feel that the account numbers in your source data could need to be cells formatted as Text, rather than General or otherwise; this is because these account numbers should never be treated as numbers proper because you're never going to do any calculations on the 'numbers' - they're identifiers only - you're never going to add two account numbers together to get anything meaningful.
    So I suggest that you make sure that the source data account numbers are text.
    Ideally, the way the source data gets into your excel sheet in the first place needs to be such that they're text from the outset. That depends on how that data gets there; can you enlighten us on that?

    Another possible fix is to to convert those account numbers to text, but it's not as straightforward as simply selecting the account numbers in the source data and altering the cells' formats.
    One way is to select those account number cells in the source data, then using Text-to-columns in the Data Tools section of the Data tab of the ribbon, and in the first 2 steps ensuring that nothing tries to split your data, and in the 3rd step choosing Text as the column Data format, and clicking Finish. If you've been successful you should see the little green triangles in the top left of each cell notifying you that these cells contain numbers formatted as text.

    Now try your code, still without trying to reformat the cells in the pivot table - I'm guessing it should be fine.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. If you use On Error Resume Next, you really should IMO go back to normal error checking as soon as possible with On Error Goto 0

    2. Easiest way is to fix the account number formatting in the data

    3. Because you left Resume Next on for the rest of the macro, some error were just ignored

    i.e.

    ActiveSheet.PivotFields("BANK NAME").Subtotals

    4. It's easier for us to see if you just include the macro in a module in the workbook, instead of pasting the text into a worksheet


    5. Try this workbook and see


    Option Explicit
    
    
    Sub InsertPivotTable()
        'Macro modified by Crabmanrocks from ExcelChamps original template. Macro to summarise payment data in reports/pivot table.
        
        
        'Declare Variables
        Dim i As Long
        Dim PSheet As Worksheet
        Dim DSheet As Worksheet
        Dim PCache As PivotCache
        Dim PTable As PivotTable
        Dim PRange As Range
        Dim LastRow As Long
        Dim LastCol As Long
        
        
        'Insert a New Blank Worksheet
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("PivotTable").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        Sheets.Add Before:=ActiveSheet
        ActiveSheet.Name = "PivotTable"
        
        Set PSheet = Worksheets("PivotTable")
        Set DSheet = Worksheets("Data")
        
        
        'Define Data Range
        LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
        
        'fix data
        With PRange     '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            For i = 2 To .Rows.Count
                .Cells(i, 6).Value = "'" & .Cells(i, 6).Text
            Next i
        End With
        
        
        'Define Pivot Cache
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=PRange, Version:=6).CreatePivotTable _
                TableDestination:=PSheet.Cells(2, 2), TableName:="PaymentsPivotTable", DefaultVersion:=6
    
    
        Set PTable = PSheet.PivotTables("PaymentsPivotTable")
        
        
        With PTable     '     <<<<<<<<<<<<<<<<<<<<<<<
            'Insert Row Fields
            With .PivotFields("Date Paid")
                .Orientation = xlRowField
                .Position = 1
            End With
            
            
            'Insert Row Fields
            With .PivotFields("CLAIMANT/BENEFICIARY")
                .Orientation = xlRowField
                .Position = 2
            End With
            
            
            'Insert Row Fields
            With .PivotFields("ACCOUNT NO:")
                .Orientation = xlRowField
                .Position = 3
            End With
            
            
            'Insert Column Fields
            With .PivotFields("BANK NAME")
                .Orientation = xlColumnField
                .Position = 1
            End With
            
            
            With .PivotFields("AMOUNT")
                .Orientation = xlDataField
                .Position = 1
                .Function = xlSum
                .NumberFormat = "#,##0"
                .Name = "Payments "
            End With
            
            
            'Format Pivot Table
            .ShowTableStyleRowStripes = True
            .TableStyle2 = "PivotStyleMedium9"
            
            
    '        With ActiveSheet.PivotFields("BANK NAME").Subtotals(1) = False     '   <<<<<<<<<<<<<
            .PivotFields("BANK NAME").Subtotals(1) = False
            
    '        .PivotFields("ACCOUNT NO:").NumberFormat = "0_;-0"  '   <<<<<<<<<<<<<<<<<<
    '        .PivotFields("ACCOUNT NO:").NumberFormat = "@"  '   <<<<<<<<<<<<<<<<<<
        End With
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Thanks P45CAL for your answer. In the end, I had to use the CONCATENATE function in the data sheet to add a space before the account number. This forced the pivot table to show the full number as opposed to the scientific notation. In addition, it was best i broke down the code into smaller modules. This helped to easily identify any errors in the code. Many thanks for your help. I am newbie to VBA and loving the support from you and the community.

  5. #5
    Thanks Paul for your answer. In the end, I had to use the CONCATENATE function in the data sheet to add a space before the account number. This forced the pivot table to show the full number as opposed to the scientific notation. In addition, it was best i broke down the code into smaller modules. This helped to easily identify any errors in the code. Many thanks for your help. I am a newbie to VBA and loving the support from you and the community.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Adding the space in front tells Excel that "This is text, so leave it alone"


    The same result if you add a single quote in front


     .Cells(i, 6).Value = "'" & .Cells(i, 6).Text
    
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •