Macro question: Using named ranges
On Nov 19, 1:13*am, TheGlimmerMan
wrote:
*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?
I'm a bit lost at what you are trying to do but:
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
|