Help with Macro looping thru columns

[spacemancw]

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 Kiosterud]

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.

 

[spacemancw]

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.

 

[mudraker]

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 & ")

 

[spacemancw]

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