Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable in a chart macro
Really new to this. Trying to get a do...loop to create a chart that shows
increasing data points with each pass. (Excel 2000) How do I get the variable row to make sense in the charting statement ActiveChart.SeriesCollection(1).XValues = "=result!R159C51:R&row&C51" so that with each loop the value for row increases from the initial value to the ending value. Under the current design it stops (debug) at the first line with the &row& reference. Sub Macro55minchart() ' ' Macro55minchart Macro ' Macro recorded 9/9/2008 by ... ' ' Keyboard Shortcut: Ctrl+Shift+O ' ' Const mydelay = 6 ' Dim row As Integer ' Dim counter As Integer ' ActiveChart.SetSourceData Source:=Sheets("result").Range("AY159:BE159"), _ PlotBy:=xlColumns ActiveChart.SeriesCollection(1).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(1).Values = "=result!R159C52:R160C52" ActiveChart.SeriesCollection(2).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(2).Values = "=result!R159C53:R160C53" ActiveChart.SeriesCollection(3).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(3).Values = "=result!R159C54:R160C54" ActiveChart.SeriesCollection(4).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(4).Values = "=result!R159C55:R160C55" ActiveChart.SeriesCollection(5).Values = "=result!R159C56:R160C56" ActiveChart.SeriesCollection(6).Values = "=result!R159C57:R160C57" Application.Wait Time + TimeSerial(0, 0, mydelay) row = 161 counter = 161 Do Until counter = 290 ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" ActiveChart.SeriesCollection(1).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(1).Values = "=result!R159C52:R&row&C52" ActiveChart.SeriesCollection(2).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(2).Values = "=result!R159C53:R&row&C53" ActiveChart.SeriesCollection(3).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(3).Values = "=result!R159C54:R&row&C54" ActiveChart.SeriesCollection(4).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(4).Values = "=result!R159C55:R&row&C55" ActiveChart.SeriesCollection(5).Values = "=result!R159C56:R&row&C56" ActiveChart.SeriesCollection(6).Values = "=result!R159C57:R&row&C57" ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" Application.Wait Time + TimeSerial(0, 0, mydelay) counter = counter + 1 row = row + 1 Loop End Sub Thanks for any help A |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable in a chart macro
Hi,
You need to add quotes in order to break the string and concatenate the variable, ActiveChart.SeriesCollection(1).XValues = _ "=result!R159C51:R" & row & "C51" Cheers Andy fryb53 wrote: Really new to this. Trying to get a do...loop to create a chart that shows increasing data points with each pass. (Excel 2000) How do I get the variable row to make sense in the charting statement ActiveChart.SeriesCollection(1).XValues = "=result!R159C51:R&row&C51" so that with each loop the value for row increases from the initial value to the ending value. Under the current design it stops (debug) at the first line with the &row& reference. Sub Macro55minchart() ' ' Macro55minchart Macro ' Macro recorded 9/9/2008 by ... ' ' Keyboard Shortcut: Ctrl+Shift+O ' ' Const mydelay = 6 ' Dim row As Integer ' Dim counter As Integer ' ActiveChart.SetSourceData Source:=Sheets("result").Range("AY159:BE159"), _ PlotBy:=xlColumns ActiveChart.SeriesCollection(1).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(1).Values = "=result!R159C52:R160C52" ActiveChart.SeriesCollection(2).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(2).Values = "=result!R159C53:R160C53" ActiveChart.SeriesCollection(3).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(3).Values = "=result!R159C54:R160C54" ActiveChart.SeriesCollection(4).XValues = "=result!R159C51:R160C51" ActiveChart.SeriesCollection(4).Values = "=result!R159C55:R160C55" ActiveChart.SeriesCollection(5).Values = "=result!R159C56:R160C56" ActiveChart.SeriesCollection(6).Values = "=result!R159C57:R160C57" Application.Wait Time + TimeSerial(0, 0, mydelay) row = 161 counter = 161 Do Until counter = 290 ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" ActiveChart.SeriesCollection(1).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(1).Values = "=result!R159C52:R&row&C52" ActiveChart.SeriesCollection(2).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(2).Values = "=result!R159C53:R&row&C53" ActiveChart.SeriesCollection(3).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(3).Values = "=result!R159C54:R&row&C54" ActiveChart.SeriesCollection(4).XValues = "=result!R159C51:R&row&C51" ActiveChart.SeriesCollection(4).Values = "=result!R159C55:R&row&C55" ActiveChart.SeriesCollection(5).Values = "=result!R159C56:R&row&C56" ActiveChart.SeriesCollection(6).Values = "=result!R159C57:R&row&C57" ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" Application.Wait Time + TimeSerial(0, 0, mydelay) counter = counter + 1 row = row + 1 Loop End Sub Thanks for any help A -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using macro to set chart title with a variable month | Excel Discussion (Misc queries) | |||
Variable Cell Value within a macro | Excel Discussion (Misc queries) | |||
Variable Macro | Excel Discussion (Misc queries) | |||
Range used in a macro needs to be variable | Excel Worksheet Functions | |||
Chart with both X & Y variable | Charts and Charting in Excel |