Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by Bove
This is a piece off code from an APP (PETRAS Reporting)written by Bovey and
others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by Bove
You can use Insert|Name to define a name that refers to a range.
Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by
Thanks for your reply Dave,
If it was a named range then that would work, no problem But it points to this formula: =VERSCHUIVING(rngConsolidate;0;0;AANTALARG(SourceD ata!$A:$A)) So know y don't understand it anymore. The APP is setup to automaticly open the template, so no file off myown and y checked for the name, it's there. I'am confused here RichNL "Dave Peterson" schreef: You can use Insert|Name to define a name that refers to a range. Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by
That formula looks like it could be the equivalent of an =offset(....,counta())
formula (in English). If that guess is correct, it's a way where you can define a name for a range that can grow and contract when you add/delete entries from a list. Debra Dalgleish has some instructions on how that kind of thing works at: http://www.contextures.com/xlNames01.html#Dynamic (in English <bg) You can test it by opening that workbook and trying this: Edit|Goto|and type: rngDataArea in the reference box. Some range should be selected -- if there's data in that range. Richard wrote: Thanks for your reply Dave, If it was a named range then that would work, no problem But it points to this formula: =VERSCHUIVING(rngConsolidate;0;0;AANTALARG(SourceD ata!$A:$A)) So know y don't understand it anymore. The APP is setup to automaticly open the template, so no file off myown and y checked for the name, it's there. I'am confused here RichNL "Dave Peterson" schreef: You can use Insert|Name to define a name that refers to a range. Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by
Sorry.should have translated it into english but your guess was correct
So,it is possible to use the formulaname in that particular statement. Y have to test some more because y can't believe it was written like this only to fail. Maybe it has to do with some settings in excel??? Because all off the examples on the cd don"t work with that particular statement. Richard "Dave Peterson" schreef: That formula looks like it could be the equivalent of an =offset(....,counta()) formula (in English). If that guess is correct, it's a way where you can define a name for a range that can grow and contract when you add/delete entries from a list. Debra Dalgleish has some instructions on how that kind of thing works at: http://www.contextures.com/xlNames01.html#Dynamic (in English <bg) You can test it by opening that workbook and trying this: Edit|Goto|and type: rngDataArea in the reference box. Some range should be selected -- if there's data in that range. Richard wrote: Thanks for your reply Dave, If it was a named range then that would work, no problem But it points to this formula: =VERSCHUIVING(rngConsolidate;0;0;AANTALARG(SourceD ata!$A:$A)) So know y don't understand it anymore. The APP is setup to automaticly open the template, so no file off myown and y checked for the name, it's there. I'am confused here RichNL "Dave Peterson" schreef: You can use Insert|Name to define a name that refers to a range. Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by
Did you try the Edit|goto stuff?
Richard wrote: Sorry.should have translated it into english but your guess was correct So,it is possible to use the formulaname in that particular statement. Y have to test some more because y can't believe it was written like this only to fail. Maybe it has to do with some settings in excel??? Because all off the examples on the cd don"t work with that particular statement. Richard "Dave Peterson" schreef: That formula looks like it could be the equivalent of an =offset(....,counta()) formula (in English). If that guess is correct, it's a way where you can define a name for a range that can grow and contract when you add/delete entries from a list. Debra Dalgleish has some instructions on how that kind of thing works at: http://www.contextures.com/xlNames01.html#Dynamic (in English <bg) You can test it by opening that workbook and trying this: Edit|Goto|and type: rngDataArea in the reference box. Some range should be selected -- if there's data in that range. Richard wrote: Thanks for your reply Dave, If it was a named range then that would work, no problem But it points to this formula: =VERSCHUIVING(rngConsolidate;0;0;AANTALARG(SourceD ata!$A:$A)) So know y don't understand it anymore. The APP is setup to automaticly open the template, so no file off myown and y checked for the name, it's there. I'am confused here RichNL "Dave Peterson" schreef: You can use Insert|Name to define a name that refers to a range. Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by
Yes y did and y saw the range but then again when y do this y get errors?
Y copy'd both names to a new workbook and try to retrieve the name off the sheet Sub parent() Dim wks As Worksheet Set wks = ThisWorkbook.Names("rngDataArea").RefersToRange.pa rent MsgBox wks.Name End Sub Richard "Dave Peterson" schreef: Did you try the Edit|goto stuff? Richard wrote: Sorry.should have translated it into english but your guess was correct So,it is possible to use the formulaname in that particular statement. Y have to test some more because y can't believe it was written like this only to fail. Maybe it has to do with some settings in excel??? Because all off the examples on the cd don"t work with that particular statement. Richard "Dave Peterson" schreef: That formula looks like it could be the equivalent of an =offset(....,counta()) formula (in English). If that guess is correct, it's a way where you can define a name for a range that can grow and contract when you add/delete entries from a list. Debra Dalgleish has some instructions on how that kind of thing works at: http://www.contextures.com/xlNames01.html#Dynamic (in English <bg) You can test it by opening that workbook and trying this: Edit|Goto|and type: rngDataArea in the reference box. Some range should be selected -- if there's data in that range. Richard wrote: Thanks for your reply Dave, If it was a named range then that would work, no problem But it points to this formula: =VERSCHUIVING(rngConsolidate;0;0;AANTALARG(SourceD ata!$A:$A)) So know y don't understand it anymore. The APP is setup to automaticly open the template, so no file off myown and y checked for the name, it's there. I'am confused here RichNL "Dave Peterson" schreef: You can use Insert|Name to define a name that refers to a range. Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefersToRange.Parent gives back an error(from book written by
Your code worked fine for me. I don't see anything wrong.
Maybe looking at: rngConsolidate would lead somewhere. This may not help you find the solution, but even if it doesn't, it's a very nice utility to have when working with names. Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make your detective work a little easier. ======= I guess the next real question is: What file did you open (include the folder name and file name) that contains the code and what file did you select (include that folder name and file name, too)? Richard wrote: Yes y did and y saw the range but then again when y do this y get errors? Y copy'd both names to a new workbook and try to retrieve the name off the sheet Sub parent() Dim wks As Worksheet Set wks = ThisWorkbook.Names("rngDataArea").RefersToRange.pa rent MsgBox wks.Name End Sub Richard "Dave Peterson" schreef: Did you try the Edit|goto stuff? Richard wrote: Sorry.should have translated it into english but your guess was correct So,it is possible to use the formulaname in that particular statement. Y have to test some more because y can't believe it was written like this only to fail. Maybe it has to do with some settings in excel??? Because all off the examples on the cd don"t work with that particular statement. Richard "Dave Peterson" schreef: That formula looks like it could be the equivalent of an =offset(....,counta()) formula (in English). If that guess is correct, it's a way where you can define a name for a range that can grow and contract when you add/delete entries from a list. Debra Dalgleish has some instructions on how that kind of thing works at: http://www.contextures.com/xlNames01.html#Dynamic (in English <bg) You can test it by opening that workbook and trying this: Edit|Goto|and type: rngDataArea in the reference box. Some range should be selected -- if there's data in that range. Richard wrote: Thanks for your reply Dave, If it was a named range then that would work, no problem But it points to this formula: =VERSCHUIVING(rngConsolidate;0;0;AANTALARG(SourceD ata!$A:$A)) So know y don't understand it anymore. The APP is setup to automaticly open the template, so no file off myown and y checked for the name, it's there. I'am confused here RichNL "Dave Peterson" schreef: You can use Insert|Name to define a name that refers to a range. Rob's code assumes that you're opening a workbook that has this range name already defined. If the workbook that you open doesn't have this name, you'll get that error. (My bet is you're opening your own file--not one of Rob's sample files???). If you have a range name, then that name refers to a range (referstorange) (kind of circular, huh). Any way, once you know the range that that the name refers to, you know what worksheet it's on. The parent of a range is the worksheet (and the parent of the worksheet is the workbook and the parent of the workbook is the application (excel itself))--it's the same kind of thing you see when you're doing the family tree. Richard wrote: This is a piece off code from an APP (PETRAS Reporting)written by Bovey and others for their book. Y hope somebody will recognize it here. The line off code "Set wksData.........."(see below) repeadedly gives back an error. The RefersToRange.Parent(Y do not completely understand it) Y hope somebody can help me with this(maybe Bovey himself) Because y sort off want to adapt the code to work with my own APP. const sSOURCE As String = "ConsolidateWorkbooks" On Error GoTo ErrorHandler 'Ask for a multi-select list of files to consolidate vFiles = Application.GetOpenFilename("PETRAS Timesheet Workbooks (*.xls), *.xls", , "Selects Workbooks to Consolidate", "Consolidate", True) 'Exit if cancelled. As we asked for a multi-select list, 'we'll get an array returned if OK, or False of Cancelled, 'so we can test for the array (OK) case: If Not IsArray(vFiles) Then Exit Sub Application.ScreenUpdating = False 'Get the sheet to write to Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange. Parent Thanks, Richard -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error on large Excel spreadsheet with VBA, written in '03 used inmixed '03 - '07 environment | Excel Discussion (Misc queries) | |||
VB??.tmp error with multiple excel worksheets in 1 book | Excel Worksheet Functions | |||
Define Names / RefersToRange | Excel Programming | |||
Using rng.Value & RefersToRange to pass data between worksheets - SLOW! | Excel Programming | |||
Hyperlink written to worksheet produces error | Excel Programming |