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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question: Using named ranges
On Nov 19, 7: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? 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question: Using named ranges
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question: Using named ranges
On Fri, 19 Nov 2010 03:34:10 -0800 (PST), Plac wrote:
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question: Using named ranges
On Fri, 19 Nov 2010 06:53:58 -0500, "Jim Rech" wrote:
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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." |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question: Using named ranges
On Fri, 19 Nov 2010 06:11:57 -0800 (PST), Don Guillett Excel MVP
wrote: 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. |
Reply |
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 |