Hello everyone!

I am trying to create a macro that will change my graph's
data range in function of a specific cell's data.

So, for example, if my cell a1 = Month3, I want my graph
data range to be from a5 to d5.

but, if my cell a1= month5, I want the graph data range to
be from a5 to F5.

I tried recording the macro and selecting my data range
using this when I go to the graph/source data/data range --
- =indirect(a1)

But then, it just takes this indirect a1 and makes it into
the given range (for example month3 turns out to make my
data range--- =a5:d5

Hope this is not too confusing.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("OEE Raw
Data").Range("A5:F6"), _
PlotBy:=xlRows

The 3rd line is what I need to change I think...
source:=indirect type of function thing...

Can anyone help?

Thanks

Pitbull

insertnamedefineenter a name such as rowrangein the refers to
=offset($a$5,0,0,1,$a$1)
in series
=yourworksheetname!rowrange

now try if 3 in a1 or 5 in a1

--
Don Guillett
SalesAid Software

"Pitbull" wrote in message
...
Hello everyone!

I am trying to create a macro that will change my graph's
data range in function of a specific cell's data.

So, for example, if my cell a1 = Month3, I want my graph
data range to be from a5 to d5.

but, if my cell a1= month5, I want the graph data range to
be from a5 to F5.

I tried recording the macro and selecting my data range
using this when I go to the graph/source data/data range --
- =indirect(a1)

But then, it just takes this indirect a1 and makes it into
the given range (for example month3 turns out to make my
data range--- =a5:d5

Hope this is not too confusing.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("OEE Raw
Data").Range("A5:F6"), _
PlotBy:=xlRows

The 3rd line is what I need to change I think...
source:=indirect type of function thing...

Can anyone help?

Thanks

Pitbull

You can also try using named ranges using the offset with count like this....

=OFFSET('FlrSpc Data'!$A$2,0,0,COUNTA('FlrSpc Data'!$A:$A) + 1,3)

You then can put the named range in the source data range field of the
graph. So as data is added, the graph grows accordingly.

HTH, Dean.

"Pitbull" wrote:

Hello everyone!

I am trying to create a macro that will change my graph's
data range in function of a specific cell's data.

So, for example, if my cell a1 = Month3, I want my graph
data range to be from a5 to d5.

but, if my cell a1= month5, I want the graph data range to
be from a5 to F5.

I tried recording the macro and selecting my data range
using this when I go to the graph/source data/data range --
- =indirect(a1)

But then, it just takes this indirect a1 and makes it into
the given range (for example month3 turns out to make my
data range--- =a5:d5

Hope this is not too confusing.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("OEE Raw
Data").Range("A5:F6"), _
PlotBy:=xlRows

The 3rd line is what I need to change I think...
source:=indirect type of function thing...

Can anyone help?

Thanks

Pitbull

Don, are you sure this will help me with the graph data
range though... played with it and still at same point.

Tx
Pittbull


-----Original Message-----
insertnamedefineenter a name such as rowrangein the
refers to
=offset($a$5,0,0,1,$a$1)
in series
=yourworksheetname!rowrange

now try if 3 in a1 or 5 in a1

--
Don Guillett
SalesAid Software

"Pitbull" wrote in
message
...
Hello everyone!

I am trying to create a macro that will change my
graph's
data range in function of a specific cell's data.

So, for example, if my cell a1 = Month3, I want my graph
data range to be from a5 to d5.

but, if my cell a1= month5, I want the graph data range
to
be from a5 to F5.

I tried recording the macro and selecting my data range
using this when I go to the graph/source data/data
range --
- =indirect(a1)

But then, it just takes this indirect a1 and makes it
into
the given range (for example month3 turns out to make my
data range--- =a5:d5

Hope this is not too confusing.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("OEE Raw
Data").Range("A5:F6"), _
PlotBy:=xlRows

The 3rd line is what I need to change I think...
source:=indirect type of function thing...

Can anyone help?

Thanks

Pitbull


.