Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Location
    Calgary
    Posts
    1
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to set the SubTotal TotalList using a variant instead of an array

    Greetings! I am currently redesigning a workbook that is tracking the daily output of a number of wells. The original tracked 100 wells, but the new version could track anywhere up to 1000 wells. I have been trying to find a method of getting around typing up to 1000 column numbers into the TotalList:=Array (3,4,5,etc.) statement.

    A sample of the worksheet looks like this:
    Sample Data.PNG

    After a lot of research I have created the following (Based on examples that appear to have worked for others) :
    Code:
    Sub WklySubtotal()
        Dim varCols()   As Variant  'array to hold column numbers
        Dim intCount    As Integer  'for..next counter
        Dim intMaxCol   As Integer  'number of columns to subtotaled
        
        Sheets("Sheet1").Select
        Cells(1, 3).Select
        Selection.End(xlToRight).Select
        intMaxCol = ActiveCell.Column
        
        ReDim varCols(intMaxCol - 2)
        
        For intCount = 3 To intMaxCol
            varCols(intCount - 3) = intCount
           ' Debug.Print intCount - 3, varCols(intCount - 3)
        Next intCount
    
        Selection.Subtotal Groupby:=1, Function:=xlAverage, TotalList:=varCols, _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    '    Selection.Subtotal Groupby:=1, Function:=xlAverage, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14), _
    '    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        Columns("B:B").Select
        Selection.EntireColumn.Hidden = True
        
    End Sub
    My sample data is based on 10 wells (the columns listed in the commented out section of code), and it works just fine when I use the version with the array values typed in, but when I try to run it using the varCols variant I get aRun Time error 1004 - Subtotal method of Range class failed.

    The Debug statement has shown me that the right column numbers are in the variant array.

    I am working in Excel 2013 on Windows 10, but I have been getting the same result in both Excel 2010 and 2016.

    Any help is greatly appreciated (I cannot help but feel that I am missing some tiny little thing!)

  2. #2
    New Lounger
    Join Date
    Feb 2010
    Location
    East Brunswick, NJ
    Posts
    15
    Thanks
    1
    Thanked 3 Times in 2 Posts
    This change seems to work - I was looking to avoid a reference to the zeroth array element. At least I saw no errors
    Norm

    Code:
    Sub WklySubtotal()
        Dim varCols()    As  Variant 'array to hold column numbers
        Dim intCount    As Integer  'for..next counter
        Dim intMaxCol   As Integer  'number of columns to subtotaled
        
        Sheets("Sheet1").Select
        Cells(1, 3).Select
        Selection.End(xlToRight).Select
        intMaxCol = ActiveCell.Column
        
        ReDim varCols(intMaxCol - 2)
        
        For intCount = 2 To intMaxCol
            varCols(intCount - 2) = intCount
           'Debug.Print intCount - 3, varCols(intCount - 3)
        Next intCount
    
        Selection.CurrentRegion.Select
        Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=varCols, _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    '    Selection.Subtotal Groupby:=1, Function:=xlAverage, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14), _
    '    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        Columns("B:B").Select
        Selection.EntireColumn.Hidden = True
        
    End Sub
    Last edited by RetiredGeek; 2016-02-05 at 10:03. Reason: Added Code Tags

  3. The Following User Says Thank You to Goldennorm For This Useful Post:

    davidmack (2016-02-05)

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
  •