none
Need help debugging: Run-time error '9': Subscript out of range RRS feed

  • Question

  • I have a macro that sorts team performance data each month and presents it in a chart for distribution. Part of my subroutine is to create a new worksheet tab based off a name, create a new line in the presentation chart with that name and finally filter the extracted data I pull each month by the name and copy that info to the appropriate members worksheet for further manipulation...

    The creation of the new member worksheet & new line on the presentation chart work as designed but when I come to the Autofilter portion it errors out with the Run-time error '9': Subscript out of range. This is the line that throws the error:

    Sheets(strExtractTab).ListObjects("Extract").Range.AutoFilter Field:=Column, Criteria1:=strMaxName
    

    strExtractTab is defined at the beginning of the subroutine and never changes: "Extract"

    Column is defined using: For Column = 6 to 10

    strMaxName is defined earlier in the process and the values are pulled from a column range on another sheet in the same workbook. 

    When I look at the Locals window I see valid data for all three variables. My only thought now is that the strMaxName value for Criteria1 is not found in the AutoFilter and is causing the error. Here is the complete section of code for this procedure:

    For Column = 6 to 10
    Sheets(strExtractTab).Select
    Sheets(strExtractTab).ListObjects("Extract").Range.AutoFilter Field:=Column, Criteria1:=strMaxName
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(strMaxName).Select
    


    Any ideas or suggestions are appreciated, let me know if I can provide any additional data or information.

    Thanks in advance

    Jason

     


    I'm new, please help. Begging for forgiveness in advance.
    Thursday, October 6, 2011 1:36 PM

Answers

  • My only thought now is that the strMaxName value for Criteria1 is not found in the AutoFilter and is causing the error.

    If strMaxName is not found then you shoud simply get no data displayed and be left with the column headers only.

    I am assuming that you are working with a table named Extract. Is this correct because if not, .ListObjects("Extract") will return an error?

    Anyway the following example is a better method of copying the visible data and I have tested it using dummy data of A to E for the strMaxName. There is no need for selecting ranges and the With/End With eliminates a lot of repetition in referencing the object.

    Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. The Copy and Paste is a single line of code.

    Sub CopyAndPasteVisibleCells()
      Dim strExtractTab As String
      Dim lngColumn As Long
      Dim strMaxName As String
     
      strExtractTab = "ExtractTab"
     
      For lngColumn = 6 To 10
       
        'Next line used for testing the example
        'It just used one uppercase character between A and E
        strMaxName = Chr(59 + lngColumn)
       
        With Sheets(strExtractTab).ListObjects("Extract")
         
          'Not sure if you need the next line to clear previous filter
          'but without it an additional filter is set in each loop
          .AutoFilter.ShowAllData
         
          .Range.AutoFilter Field:=lngColumn, Criteria1:=strMaxName
         
          'Copy range and paste to destination in one line.
          .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=Sheets(strMaxName).Range("A1")
       
        End With
     
      Next lngColumn

    End Sub

     


    Regards, OssieMac
    • Marked as answer by JMStumpf Friday, October 14, 2011 2:15 AM
    Monday, October 10, 2011 3:37 AM

All replies

  • Hi Jason,

    I can recommend changing your variable Column to some other name - for example - varColumn. Using reserved words as a varable name can cause problems in the code.

     

    Nadia

     

    Sunday, October 9, 2011 1:25 AM
  • My only thought now is that the strMaxName value for Criteria1 is not found in the AutoFilter and is causing the error.

    If strMaxName is not found then you shoud simply get no data displayed and be left with the column headers only.

    I am assuming that you are working with a table named Extract. Is this correct because if not, .ListObjects("Extract") will return an error?

    Anyway the following example is a better method of copying the visible data and I have tested it using dummy data of A to E for the strMaxName. There is no need for selecting ranges and the With/End With eliminates a lot of repetition in referencing the object.

    Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. The Copy and Paste is a single line of code.

    Sub CopyAndPasteVisibleCells()
      Dim strExtractTab As String
      Dim lngColumn As Long
      Dim strMaxName As String
     
      strExtractTab = "ExtractTab"
     
      For lngColumn = 6 To 10
       
        'Next line used for testing the example
        'It just used one uppercase character between A and E
        strMaxName = Chr(59 + lngColumn)
       
        With Sheets(strExtractTab).ListObjects("Extract")
         
          'Not sure if you need the next line to clear previous filter
          'but without it an additional filter is set in each loop
          .AutoFilter.ShowAllData
         
          .Range.AutoFilter Field:=lngColumn, Criteria1:=strMaxName
         
          'Copy range and paste to destination in one line.
          .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=Sheets(strMaxName).Range("A1")
       
        End With
     
      Next lngColumn

    End Sub

     


    Regards, OssieMac
    • Marked as answer by JMStumpf Friday, October 14, 2011 2:15 AM
    Monday, October 10, 2011 3:37 AM
  • I took your advice and changed it to Dim varColumn as Variant. Still getting the error but a post below helped me move beyond this error and on to another. Thanks for your help!

    Jason


    I'm new, please help. Begging for forgiveness in advance.
    Friday, October 14, 2011 2:04 AM
  • You had me at ListObjects... My table was not named and in fact wasn't even a formatted table but simply a data/value paste of the 'extracted' data from my database.  I was using the .ListObjects("Extract") to reference the sheet name. I have since formatted and renamed the table to Extract and it worked... At least for my Run-Time error '9'

    Now I'm running into another issue where some of my team members have very long names and I seem to be exceeding some character limit or something. Looks like I will have to use their last name only for my individual sheet break outs.

    Run-time error '1004':

    You typed an invalid name for a sheet or chart. Make sure that:

    -The Name that you type does not exceed 31 characters. (Her name IS more than 31 characters)

    -The name does not contain any of the following characters:  : \ / ? * [ or ]  (None of these in the name)

    -You did not leave the name blank.  (Name is not blank)

    Either way thank you very much for the insight, I am still learning VBA but each bug I nail down makes me very excited!

    Jason


    I'm new, please help. Begging for forgiveness in advance.
    • Edited by JMStumpf Friday, October 14, 2011 2:16 AM
    Friday, October 14, 2011 2:15 AM
  • Yes. The problem with the worksheet names appears to be the length of the name.

    Because you used the ListObject in your original example, I assumed that you had tables in the worksheet. In many ways a table in a worksheet is like a mini worksheet within the main worksheet.

    If you simply wanted to use AutoFilter in a worksheet without using tables then it would be like the following. Note the test to see if AutoFilter is turned on because the command to turn AutoFilter on is a Toggle command. (ie. It turns AutoFilter On if NOT already on and turns AutoFilter Off if Autofilter is already on.)

    Sub CopyAndPasteVisibleCells_2()
      Dim strExtractTab As String
      Dim lngColumn As Long
      Dim strMaxName As String
     
      'ExtractTab is the name of the worksheet.
      strExtractTab = "ExtractTab"
     
      For lngColumn = 6 To 10
       
        'Next line used for testing the example
        'It just uses one uppercase character between A and E.
        strMaxName = Chr(59 + lngColumn)
       
        With Sheets(strExtractTab)
          'Test if AutoFilter is off and if Off, then turn on.
          If .AutoFilterMode = False Then
            .UsedRange.AutoFilter   'Turn on AutoFilter
          End If
         
          'Remove any existing filters that have been set.
          .AutoFilter.ShowAllData
         
          .AutoFilter.Range.AutoFilter Field:=lngColumn, _
                        Criteria1:=strMaxName
         
          'Copy range and paste to destination in one line.
          .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=Sheets(strMaxName).Range("A1")
       
        End With
     
      Next lngColumn

    End Sub

     


    Regards, OssieMac
    Friday, October 14, 2011 3:51 AM