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

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