Macro question: Using named ranges

[TheGlimmerMan]

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?

 

[Plac]

 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?

You don't want to be messing around with named ranges. A range is an
array, and an array isa much simpler thing to process. HTH

 

[Jim Rech]

I must confess to having lost my way halfway through your discussion. But
just looking at the problem line of code:

..Resize .HeaderRowRange.CurrentRegion

I would think you'd need to do something like this because the Resize method
is looking for a number:

..Resize .HeaderRowRange.CurrentRegion.Rows.Count

 

[TheGlimmerMan]

You don't want to be messing around with named ranges. A range is an
array, and an array isa much simpler thing to process. HTH

That answer proves that you are just a goddamned retard. HTH, dumbass.

Idiots like you that give answers that are anything but an answer, and
that are NEVER anything even remotely related to the question, needs to
keep your retarded ass out of help groups, because "help" *you* ain't.

 

[TheGlimmerMan]

I must confess to having lost my way halfway through your discussion. But
just looking at the problem line of code:

.Resize .HeaderRowRange.CurrentRegion

I would think you'd need to do something like this because the Resize method
is looking for a number:

.Resize .HeaderRowRange.CurrentRegion.Rows.Count

That produces a type mismatch error. error 13

I can't figure out how it used to work.

I add data at times. Perhaps that changes the "table" declarations and
causes the error?

 

[Don Guillett Excel MVP]

 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."

 

[TheGlimmerMan]

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."

I fill a "form" with a list of actor names that are resolved by looking
at four worksheets that are laid out like a database table.

Since there is more than one actor for a film, the table has to look up
all the actor numbers in the actor index worksheet (actor numbers, and
film numbers, and then go grab the names that that list of numbers
correspond to in the actors worksheet. This keeps from repeating actor
names in full length in the index worksheet. The number takes up far less
space, and the actors only need be listed once.

So, I grab the Director name from two similar tables that appears fine
in another cell as most films are directed by a singular person (I know,
error) in another cell in my "main" worksheet, which is a sheet that has
lookups of all the data for a given film in the "listing" worksheet.

That way I see a single set of data for one film, even though the
worksheet that has all the 'fields' for several hundred thousand films.

Sorry, but it is a 40MB workbook, and you likely do not need it to help
me. So, I will give you this shot of my "main page" Which may further
your comprehension of my workbook structure.

It is MOSTLY flat file, single workbook, however, as we all know
there is no need to redundantly name the director over and over again in
the main file when the film number and director name can be the only
'list', or 'table' needed, hence the "Diectors Index", and Director
Names" worksheets. The actors are typically plural in a film, and the
single flat file is not conducive to large blocks of allocated 'open
space' to make way for the actor name(s)<problem.

SO, there is an "Acted_In" worksheet and an "Actors" worksheet.

The index is as long as the film list, several hundred thousand
entries.

The actors that match up with that cross reference only need be listed
once.

So, with this perfect homemade database engine in place, I can track
300,000 DVDs, Blu Ray, HD DVD, etc by simply showing one listing on a
form like sheet, and having the director and actorS<< looked up.

This script *should* perform the multiple actor list lookup and fill it
into a table or range nearby on the main ws, Which I then DO have a list
of empty cells that only show data when the formula resolves an actual
name from the nearby table that was populated. It should populate
a small table on my main sheet, which then fills by having a live lookup
on those cells (the filled table)

Unless there is some easy way to show matched "records" in a worksheet
that are plural and a subsequent (and different) worksheet lookup from
the initial match.

Here is my 'view form' or whatever you folks call it.

Also, as a side note, this is faster than your Access database app. It
is miserable at lookups (speed wise)in record arrays this large. Excel is
nearly instantaneous.

Here is a screen shot:

http://www.mediafire.com/i/?u59urez0w7ta23u

The "Actors" banner is what one clicks to initiate the lookup script.

Table4 is to the right of the area where the name array gets filled in,
and those columns are usually 'off screen'. The other tables are in
their corresponding worksheets.

There are 'buttons' for me to DL new database updates, and to convert
and import them. They come from ASCII files.

Used to work great, so I fear the problem is after the import of new
records. My conversion scripts have not changed though, so it must be
some array declaration and subsequent mismatch.