Re: 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(SourceData!$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
> > work*** it's on.
> >
> > The parent of a range is the work*** (and the parent of the work*** 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 Time*** 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 *** to write to
> > > Set wksData = ActiveWorkbook.Names("rngDataArea").RefersToRange.Parent
> > >
> > > Thanks,
> > > Richard
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson
.