+ Reply to Thread
Results 1 to 5 of 5

Runtime Error 9 with List Objects

Hybrid View

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    Office 365
    Posts
    67

    Runtime Error 9 with List Objects

    I am trying to create a automatic filter with listobjects in excel VBA but it is giving me a runtime error 9. I have checked to make sure that all of the sheet names and table names match but I can't figure it out.

    Error is thrown on this line

    Set lo = qws.ListObjects("filter")
    Sub filter()
    Dim qws As Worksheet 'Power Query
    Dim treews As Worksheet 'Element Tree worksheet
    Dim pos As String 'position string used to search in the query worksheet
    Dim lo As ListObject 'area to filter
    
    Set qws = ThisWorkbook.Worksheets("PQuery")
    Set treews = ThisWorkbook.Worksheets("Element Tree")
    Set usedrng = qws.UsedRange
    
    'record position that will be recognized by query worksheet
    pos = treews.Cells(4, 2)
    Debug.Print pos
    
    Set lo = qws.ListObjects("filter")
    
    'clear the autofilters from the query worksheet
    qws.AutoFilterMode = False
    
    'Filter by position for "all" and "BT", research how to make these user inputs so it is
    'Easy to switch from one position to another
    lo.Range.AutoFilter Field:=3, Criteria1:="=**All**", Operator:=xlOr, _
    Criteria2:="=*" & pos & "*"
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Runtime Error 9 with List Objects

    In the uploaded workbook there are no listobjects on the sheet PQuery, filter refers to a named range on that sheet.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    Office 365
    Posts
    67

    Re: Runtime Error 9 with List Objects

    The data in my actual workook (can't share because has sensitive data) with the tab titled PQuery comes from a Power Query. How can I refer to this data as a listobject?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Runtime Error 9 with List Objects

    If you are sure it's a listobject, and I'm not 100% sure it will be, you could try using ListObjects(1).

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    Office 365
    Posts
    67

    Re: Runtime Error 9 with List Objects

    That worked perfectly! Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Runtime error 70. Could not set the list property
    By Sintek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-03-2016, 04:35 AM
  2. Assigning properties to objects at Runtime
    By bizmita in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-06-2015, 06:23 PM
  3. Runtime Error 1004 on list creating macro
    By taipalam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2014, 04:13 AM
  4. [SOLVED] Runtime Error 1004 when Username not in list
    By ZuneidDassu in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-15-2014, 12:13 AM
  5. List of Runtime Error Codes
    By DCSwearingen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2013, 09:13 PM
  6. Error 1004 on List Sheet Objects macro.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2011, 10:38 PM
  7. [SOLVED] Dependent List in Data Validation: Runtime Error
    By cLiffordiL in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-04-2006, 01:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1