Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question: Using named ranges
Hey guys. I have a macro which performs lookups to list the director and actor names in a film. I have named ranges for the film to director name, and I have named ranges for the film to actor(s) list. I want to use the named ranges instead of tables, if possible. I think some of the tables are required though. I currently get errors related to overlapping unequal sized tables of data. I currently use the macro to declare or create the data tables (I think). So, the director index grabs the director number, and the director names list grabs the name from that number. This is easy. The actors listing, however, is a bit more difficult because it has to compile a list first then fill a table with it, the copy that into my main worksheet. I have named ranges which encompass entire columns, because the table gets new entries placed into it all the time. I assumed that this would solve that. So, I have named ranges for: The director index as: DIndex The director names are as: Directors The actor's listing are as: Actors The table for the actors and film reference has been defined as a table. Next to that table, a table gets filled with the actors numbers that match the film number. That table is what I use to perform lookups into to fill my "actors list in my main worksheet. Here is my macro. I get the error on the line that resizes a table ..Resize .HeaderRowRange.CurrentRegion Macro: Sub FilterOnVar() Dim rngData As Range Dim rngCriteria As Range Dim rngExtract As Range Dim s As String Application.ScreenUpdating = False With Sheets("Acted_In") Set rngData = .Range("Table1[#All]") Set rngCriteria = .Range("Table3[#All]") End With With Sheets("Master_Pane") Set rngExtract = .Range("Table4[#Headers]") rngData.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=rngCriteria, _ CopyToRange:=rngExtract With .ListObjects("Table4") If .ListColumns(1).DataBodyRange.Cells(1) < "" Then .Resize .HeaderRowRange.CurrentRegion End If End With End With End Sub I do not know why I am getting errors. It used to work. I get "Runtime error # 1004" (invalid range). Is there a better way to perform these lookups, and fill a table with the queried actor listing? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
named ranges question | Excel Worksheet Functions | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Named ranges - column/row question | Excel Discussion (Misc queries) | |||
named Ranges question | Excel Worksheet Functions | |||
an easy question (to some of you, but not to me) on named ranges | Excel Programming |