I used do a lot of macros before Visual Basic in Excel. Since th
arrival of the Visual Basic editor I have never had to edit
macro….until now. I just can’t find the name of what I am looking fo
to fix it so I’ll describe what I am doing and maybe somebody can hel
me.
I do a lot of spot charts in Excel. I am a Realtor so I will grap
large sets of data to compare several hundred to several thousand sale
on a scatter chart. I have a way I like to format them that is clea
for others to read that takes about 40 steps so I want to do a macro.
I start with new data each time just setting there selected in
spreadsheet. Before graphing a data set I just select it. It is at tha
point that I would like to start the macro and let it select all of m
preferences and so on.
The problem is that each time I start with a new data set on a ne
spreadsheet and the macro is looking for an absolute reference to th
sheet that I recorded the macro on. Unfortunately the spreadsheet
recorded on is no longer a valid stating place. If I rerun the macr
from the original worksheet everything works great. Once I close tha
sheet and open a new data set I get an error.
It seem that I need to edit the line to not have any reference in it
Kind of the opposite of an absolute reference.

Error is: runtime error 9 – subscript out of range

ActiveChart.SetSourceData Source:=Sheets("BERKLEY").Range("A1:B873")
PlotBy _
:=xlColumns

The above line is copied out of the Visual basic editor.
The source "BERKLEY" was the name of the worksheet I recorded in.
need that to be undetermined.
I like to select the Range before I run the macro and have the macr
just use that range.

If I could change the reference in this line I think this would wor
fine. Thank for any help you might be able to give me.
Jeff Whitbey


--
Message posted from http://www.ExcelForum.com

Hi Jeff,

Change Sheets("BERKLEY") to ActiveSheet if the selected range will
always be the same. If the selected range will be different then it may be
better to replace Sheets("BERKLEY").Range("A1:B873") with Selection instead.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jeff Whitbey " wrote in
message ...
I used do a lot of macros before Visual Basic in Excel. Since the
arrival of the Visual Basic editor I have never had to edit a
macro..until now. I just can't find the name of what I am looking for
to fix it so I'll describe what I am doing and maybe somebody can help
me.
I do a lot of spot charts in Excel. I am a Realtor so I will graph
large sets of data to compare several hundred to several thousand sales
on a scatter chart. I have a way I like to format them that is clear
for others to read that takes about 40 steps so I want to do a macro.
I start with new data each time just setting there selected in a
spreadsheet. Before graphing a data set I just select it. It is at that
point that I would like to start the macro and let it select all of my
preferences and so on.
The problem is that each time I start with a new data set on a new
spreadsheet and the macro is looking for an absolute reference to the
sheet that I recorded the macro on. Unfortunately the spreadsheet I
recorded on is no longer a valid stating place. If I rerun the macro
from the original worksheet everything works great. Once I close that
sheet and open a new data set I get an error.
It seem that I need to edit the line to not have any reference in it.
Kind of the opposite of an absolute reference.

Error is: runtime error 9 - subscript out of range

ActiveChart.SetSourceData Source:=Sheets("BERKLEY").Range("A1:B873"),
PlotBy _
:=xlColumns

The above line is copied out of the Visual basic editor.
The source "BERKLEY" was the name of the worksheet I recorded in. I
need that to be undetermined.
I like to select the Range before I run the macro and have the macro
just use that range.

If I could change the reference in this line I think this would work
fine. Thank for any help you might be able to give me.
Jeff Whitbey



---
Message posted from http://www.ExcelForum.com/

Thanks, but I am still hitting a problem. It is probably something small
and stupid. As complex problems have more visible solutions.
I tried that a few different ways
ActiveChart.SetSourceData
Source:=Sheets(“Sheetactive”).Range(“selected”), PlotBy
Also with out the quotes like below.
I get an error:
Compile editor:
Expected named paramiter
I also tried saving the macro in a worksheet I could open and paste the
data into but I still need to have a variable range. I also tried using
the file name instead of sheetactive.
This seems the most likely text. Can you see my errors?

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets(Sheetactive).Range(selected), PlotBy
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Sale Price vs Home Size"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Sale
Price"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Square
Feet"

Thanks
Jeff Whitbey



---
Message posted from http://www.ExcelForum.com/

Hi Jeff,

Referring to your line:

Source:=Sheets(Sheetactive).Range(selected), PlotBy


To identify the current sheet , use:

ActiveSheet

and for the selected range, use:

Selection

As Rob Bovey suggested, with your chart plot data selected, you can refer to
the plot range with:
Selection
thus obviating the need to specify different plot ranges for different
charts.

So, incorporating this into your chart code to provide a procedure which
will produce a chart for any selected data, you have somethiing like:

Sub tester()
Dim rng As Range

Set rng = Selection

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Sale Price vs Home Size"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text _
= "Sale Price "
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text _
= "Square Feet "
End With

End Sub

For your information, each full line between
With ActiveChart
and
End With
must have an initial period.


---
Regards,
Norman



"Jeff Whitbey " wrote in
message ...
Thanks, but I am still hitting a problem. It is probably something small
and stupid. As complex problems have more visible solutions.
I tried that a few different ways
ActiveChart.SetSourceData
Source:=Sheets("Sheetactive").Range("selected"), PlotBy
Also with out the quotes like below.
I get an error:
Compile editor:
Expected named paramiter
I also tried saving the macro in a worksheet I could open and paste the
data into but I still need to have a variable range. I also tried using
the file name instead of sheetactive.
This seems the most likely text. Can you see my errors?

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets(Sheetactive).Range(selected), PlotBy
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "Sale Price vs Home Size"
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Sale
Price"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Square
Feet"

Thanks
Jeff Whitbey



---
Message posted from http://www.ExcelForum.com/

Rob and Norman,
Thank you!
I was able to get that macro working real well and fixed the other on
the I was working around.
I appreciate your help.
Thank

--
Message posted from http://www.ExcelForum.com