Results 1 to 5 of 5

Runtime Error 9 with List Objects

Threaded View

kelseygueldalewis Runtime Error 9 with List... 05-31-2018, 01:42 PM
Norie Re: Runtime Error 9 with List... 05-31-2018, 01:48 PM
kelseygueldalewis Re: Runtime Error 9 with List... 05-31-2018, 01:57 PM
Norie Re: Runtime Error 9 with List... 05-31-2018, 02:09 PM
kelseygueldalewis Re: Runtime Error 9 with List... 05-31-2018, 02:11 PM
  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

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] [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