RefersToRange.Parent gives back an error(from book written by Bove

[Richard]

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]

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]

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:

 

[Dave Peterson]

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]

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:

 

[Dave Peterson]

Did you try the Edit|goto stuff?

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:

 

[Richard]

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.parent
MsgBox wks.Name
End Sub

Richard
"Dave Peterson" schreef:

 

[Dave Peterson]

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)?

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.parent
MsgBox wks.Name
End Sub

Richard
"Dave Peterson" schreef: