How to retrieve range in formula?

[deko]

I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in the
cell's formula until the cell is selected. Is there any way to retrieve the
range that's contained in the cells formula?

Thanks in advance.

 

[Tom Ogilvy]

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

 

[deko]

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy

I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in the
cell's formula until the cell is selected. Is there any way to retrieve the
range that's contained in the cells formula?

Thanks in advance.

 

[deko]

? ActiveCell.Formula

=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

Thanks for the quick reply. I'll give it a shot and post back.

 

[R.VENKATARAMAN]

great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy

I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in the
cell's formula until the cell is selected. Is there any way to retrieve the
range that's contained in the cells formula?

Thanks in advance.

 

[deko]

You can use the directprecedents property

I put this code in a module and call it from a button on Sheet1 - seems to
be working okay...

Public Sub CreateChart(bytChart As Byte)
Dim sChart As Chart
Dim sRange As Range
Set sRange = Application.ActiveWindow.ActiveCell.DirectPrecedents
Sheet2.ChartObjects(1).Delete
Set sChart = Charts.Add
With sChart
.chartType = xlBarClustered
.SetSourceData Source:=sRange, PlotBy:=xlRows
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Location xlLocationAsObject, "Sheet2"
End With
With Sheet2.ChartObjects(1)
.Width = 600
.Height = 400
.Top = 20
.Left = 100
End With
End Sub

Thanks for the help!

 

[Tom Ogilvy]

If your asking what is directprecedents, it is any ranges that the formula
in the cell depends on.

However, It doesn't work for linked to cells - cells on other worksheets, on
cells/ranges on the same sheet.

--
Regards,
Tom Ogilvy

great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy

I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in the
cell's formula until the cell is selected. Is there any way to

retrieve
the

range that's contained in the cells formula?

Thanks in advance.

 

[deko]

However, It doesn't work for linked to cells - cells on other worksheets,
on

cells/ranges on the same sheet.

Yes, I discovered this limitation - which conflicts with the requirements I
was given. I don't see why the Excel team couldn't make this work.

 

[R.VENKATARAMAN]

thanks Mr. Ogilvy.
I have understood the term <directprecedents>. But when i went to help(vba)
of this term I find another term <directdependents> in <see also> of this
help.
I would like to know the utility of this function. regards

mine excel 2000
===============

If your asking what is directprecedents, it is any ranges that the formula
in the cell depends on.

However, It doesn't work for linked to cells - cells on other worksheets, on
cells/ranges on the same sheet.

--
Regards,
Tom Ogilvy

great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified

in
the

cell's formula until the cell is selected. Is there any way to retrieve
the
range that's contained in the cells formula?

Thanks in advance.

 

[Tom Ogilvy]

I would surmise the problem is somehow concerned with the fact that it
returns a single range object. This would work if all the precedents were
on a single sheet - either the same sheet as the formula or on the same
external sheet. However, if the arguments involved a mix of sheets, then a
single range object can not address multiple sheets. Perhaps the function
should return an array.

 

[Tom Ogilvy]

DirectDependents are those cells for which the cell in question is a
DirectPrecedent

if A1 had the formula
=C1

Range("A1").DirectPrecedents
would return a range reference to C1

Range("C1").DirectDependents
would return a range reerence to A1

as shown from the immediate window:


? Range("A1").Formula
=C1
? Range("A1").DirectPrecedents.Address
$C$1
? Range("C1").DirectDependents.Address
$A$1

--
Regards,
Tom Ogilvy

thanks Mr. Ogilvy.
I have understood the term <directprecedents>. But when i went to help(vba)
of this term I find another term <directdependents> in <see also> of this
help.
I would like to know the utility of this function. regards

mine excel 2000
===============

If your asking what is directprecedents, it is any ranges that the formula
in the cell depends on.

However, It doesn't work for linked to cells - cells on other

worksheets,

on
cells/ranges on the same sheet.

--
Regards,
Tom Ogilvy

great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


I need to create a chart of values that are contained in a cell's
formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is

specified

in