Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem. Need a vague not absolute reference
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem. Need a vague not absolute reference
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem. Need a vague not absolute reference
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem. Need a vague not absolute reference
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem. Need a vague not absolute reference
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute cell reference macro | Excel Discussion (Misc queries) | |||
Absolute Reference Problem... | Excel Discussion (Misc queries) | |||
Problem with Absolute reference | Excel Discussion (Misc queries) | |||
Excel too helpful! (problem with absolute reference) | Excel Worksheet Functions | |||
Creating an Absolute Reference Macro | Excel Programming |