Help with Macro looping thru columns

Below is the macro that I currently use and it works well

Starting at column(i) it draws a graph with Y axis of column(i) and X
axis of column(1)
The macro starts of by declaring the value of i

--------------------------------------------
Dim i As Integer
i = 2
Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
i = i + 1

Loop
--------------------------------------------

I have a different data set now where I am interested in XY graphing
columns 4 and 3 .... 6 and 5 ... 8 and 7 .... 10 and 9 .. and so on
But the final amount of pairs can vary in each data set.
Now I wanna change the macro so that Y axis is column(i) and X axis is
column(i - 1)

--------------------------------------------
Dim i As Integer
i = 4
Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(i -
1):R360C(i - 1)"
i = i + 2

Loop
--------------------------------------------


As you can see I've refered to the column as C(i - 1) .... this
doesn't work .. obviously not the correct syntax
Do you know how to get this part to work?

thanx
Roger

Sub Manycols()
'
' Manycols Macro
' Macro recorded 6/23/2003 by djason
'

Dim i As Integer
i = 4



Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
z = i - 1
Charts.Add
'ActiveChart.ChartType = xlLine
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
PlotBy:=xlColumns
'ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2Ci:R360Ci"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasLegend = False
With ActiveChart
.HasTitle = True
'.ChartTitle.Characters.Text = "Ratio1"
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
'.MinimumScale = 0
'.MaximumScale = 3
'.MinorUnit = 0.02
'.MajorUnit = 1
.Crosses = xlCustom
.CrossesAt = 0
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
i = i + 2


Loop
End Sub

Earl
Wed Apr 28 17:20:49 CDT 2004

Spaceman,

I haven't looked closely, but do notice that you're looping through various
columns, using i-1, but using SeriesCollection(1) in each iteration.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"spacemancw" <spacemancw@yahoo.com> wrote in message
news:fbb1f69b.0404281216.e07073f@posting.google.com...
> Below is the macro that I currently use and it works well
>
> Starting at column(i) it draws a graph with Y axis of column(i) and X
> axis of column(1)
> The macro starts of by declaring the value of i
>
> --------------------------------------------
> Dim i As Integer
> i = 2
> Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
>
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
> PlotBy:=xlColumns
> ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
> i = i + 1
>
> Loop
> --------------------------------------------
>
> I have a different data set now where I am interested in XY graphing
> columns 4 and 3 .... 6 and 5 ... 8 and 7 .... 10 and 9 .. and so on
> But the final amount of pairs can vary in each data set.
> Now I wanna change the macro so that Y axis is column(i) and X axis is
> column(i - 1)
>
> --------------------------------------------
> Dim i As Integer
> i = 4
> Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
>
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
> PlotBy:=xlColumns
> ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(i -
> 1):R360C(i - 1)"
> i = i + 2
>
> Loop
> --------------------------------------------
>
>
> As you can see I've refered to the column as C(i - 1) .... this
> doesn't work .. obviously not the correct syntax
> Do you know how to get this part to work?
>
> thanx
> Roger
>
> Sub Manycols()
> '
> ' Manycols Macro
> ' Macro recorded 6/23/2003 by djason
> '
>
> Dim i As Integer
> i = 4
>
>
>
> Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
> z = i - 1
> Charts.Add
> 'ActiveChart.ChartType = xlLine
> ActiveChart.ChartType = xlXYScatterLines
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
> PlotBy:=xlColumns
> 'ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
> ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2Ci:R360Ci"
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> ActiveChart.HasLegend = False
> With ActiveChart
> .HasTitle = True
> '.ChartTitle.Characters.Text = "Ratio1"
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
> ActiveChart.Axes(xlValue).Select
> With ActiveChart.Axes(xlValue)
> '.MinimumScale = 0
> '.MaximumScale = 3
> '.MinorUnit = 0.02
> '.MajorUnit = 1
> .Crosses = xlCustom
> .CrossesAt = 0
> .ReversePlotOrder = False
> .ScaleType = xlLinear
> .DisplayUnit = xlNone
> End With
> ActiveChart.PlotArea.Select
> With Selection.Border
> .ColorIndex = 16
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With
> Selection.Interior.ColorIndex = xlNone
> i = i + 2
>
>
> Loop
> End Sub

spacemancw
Wed Apr 28 23:23:39 CDT 2004

SeriesCollection(1) seems to be fine ..... it works in the original Macro
and if I leave the line
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
as it is ...... it uses column(1) in each graph as the X axis.
Again .... I want column(i-1) to be the X axis in each graph.


"Earl Kiosterud" <nowhere@nowhere.com> wrote in message news:<utVXR8WLEHA.3324@TK2MSFTNGP10.phx.gbl>...
> Spaceman,
>
> I haven't looked closely, but do notice that you're looping through various
> columns, using i-1, but using SeriesCollection(1) in each iteration.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "spacemancw" <spacemancw@yahoo.com> wrote in message
> news:fbb1f69b.0404281216.e07073f@posting.google.com...
> > Below is the macro that I currently use and it works well
> >
> > Starting at column(i) it draws a graph with Y axis of column(i) and X
> > axis of column(1)
> > The macro starts of by declaring the value of i
> >
> > --------------------------------------------
> > Dim i As Integer
> > i = 2
> > Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
> >
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
> > PlotBy:=xlColumns
> > ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
> > i = i + 1
> >
> > Loop
> > --------------------------------------------
> >
> > I have a different data set now where I am interested in XY graphing
> > columns 4 and 3 .... 6 and 5 ... 8 and 7 .... 10 and 9 .. and so on
> > But the final amount of pairs can vary in each data set.
> > Now I wanna change the macro so that Y axis is column(i) and X axis is
> > column(i - 1)
> >
> > --------------------------------------------
> > Dim i As Integer
> > i = 4
> > Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
> >
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
> > PlotBy:=xlColumns
> > ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(i -
> > 1):R360C(i - 1)"
> > i = i + 2
> >
> > Loop
> > --------------------------------------------
> >
> >
> > As you can see I've refered to the column as C(i - 1) .... this
> > doesn't work .. obviously not the correct syntax
> > Do you know how to get this part to work?
> >
> > thanx
> > Roger
> >
> > Sub Manycols()
> > '
> > ' Manycols Macro
> > ' Macro recorded 6/23/2003 by djason
> > '
> >
> > Dim i As Integer
> > i = 4
> >
> >
> >
> > Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))
> > z = i - 1
> > Charts.Add
> > 'ActiveChart.ChartType = xlLine
> > ActiveChart.ChartType = xlXYScatterLines
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns(i),
> > PlotBy:=xlColumns
> > 'ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R360C1"
> > ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2Ci:R360Ci"
> > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> > ActiveChart.HasLegend = False
> > With ActiveChart
> > .HasTitle = True
> > '.ChartTitle.Characters.Text = "Ratio1"
> > .Axes(xlValue, xlPrimary).HasTitle = False
> > End With
> > ActiveChart.Axes(xlValue).Select
> > With ActiveChart.Axes(xlValue)
> > '.MinimumScale = 0
> > '.MaximumScale = 3
> > '.MinorUnit = 0.02
> > '.MajorUnit = 1
> > .Crosses = xlCustom
> > .CrossesAt = 0
> > .ReversePlotOrder = False
> > .ScaleType = xlLinear
> > .DisplayUnit = xlNone
> > End With
> > ActiveChart.PlotArea.Select
> > With Selection.Border
> > .ColorIndex = 16
> > .Weight = xlThin
> > .LineStyle = xlContinuous
> > End With
> > Selection.Interior.ColorIndex = xlNone
> > i = i + 2
> >
> >
> > Loop
> > End Sub

mudraker
Thu Apr 29 02:04:37 CDT 2004

I have never played around with graphs but I think this line of code

ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(i -
1):R360C(i - 1)"


should be


ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(" & i -
1 & "):R360C(" & i - 1 & ")

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

spacemancw
Thu Apr 29 23:02:35 CDT 2004

Ok ..... I figure it all out .. plus added a way to stagger the graphs
as they are created .....


Sub Macro1()
'
''
' Manycols Macro
' Macro recorded 6/23/2003 by djason
'

' This is plotting X Y graphs for
' columns 3 and 4, 5 and 6, 7 and 8, 9 and 10 and so on
' I start at column 4. This will be the first Y axis
' i - 1 will be the X axis

Dim i As Integer
i = 4

' a and b are the co-ordinates for the graph location
' they increase by 10 in the loop to stagger the grahps

Dim a As Integer
a = 5

Dim b As Integer
b = 5


Do While (Not IsEmpty(ActiveSheet.Cells(2, i)))

' evaluate i - 1
z = i - 1

Dim xRan, yRan, sht As Worksheet

Set sht = ActiveWorkbook.Worksheets(1)
sht.Select

Set xRan = Range(Cells(2, z), Cells(360, z))
Set yRan = Range(Cells(2, i), Cells(360, i))
Set nRan = Range(Cells(1, i), Cells(1, i))

' the graph co-ordinates, width and height
sht.ChartObjects.Add(a, b, 375, 215).Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection(1)
.XValues = xRan
.Values = yRan
.Name = nRan
End With

ActiveChart.Location Where:=xlLocationAsObject, Name:=sht.Name
ActiveChart.HasLegend = False
With ActiveChart
.HasTitle = True
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)

' Optional properties
'.MinimumScale = 0
'.MaximumScale = 3
'.MinorUnit = 0.02
'.MajorUnit = 1
.Crosses = xlCustom
.CrossesAt = 0
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone

' Increment variables

i = i + 2
a = a + 10
b = b + 10

' Stagger 10 graphs down the screen then return to
' the top of the screen

If a = 105 Then
b = 5
End If

If a = 205 Then
b = 5
End If


Loop

'
End Sub



mudraker <<mudraker.15gq1n@excelforum-nospam.com>> wrote in message news:<mudraker.15gq1n@excelforum-nospam.com>...
> I have never played around with graphs but I think this line of code
>
> ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(i -
> 1):R360C(i - 1)"
>
>
> should be
>
>
> ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C(" & i -
> 1 & "):R360C(" & i - 1 & ")"
>
>
> ---
> Message posted from http://www.ExcelForum.com/