How do i change the graph source data to a variable dependent on the value in a cell

[icemonkeysteve]

My problem is to do with the data that my graph uses and making it
variable.

I'm writing a program that models the trajectory of a golf ball (a lot
more complicated than it sounds) using macros in excel. The outputs of
the program are deposited in columns, the first of which is dependent
on the value in a cell. For example if the value in the cell is 4 then
the data will start depositing in long columns from column D. This
enables several sets of data to be laid down alongside each other. This
part of the program works fine.

In addition to this I have, using macro recorder, set up a button and
related macro to draw and format a graph to show the trajectory. This
is also working fine.

What I want to be able to do is plot the graphs using the value in a
given cell as a pointer for the source data of the column. So far I
have read in the value from the cell and stored it as a variable,
'col'. The problem lies in that I don't know how to change the set
column references (C4 and C5) to variable ones (something like col and
col+1).


ActiveChart.SeriesCollection(1).XValues = "=Sheet1!C4"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!C5"

This is probably really simple to solve so please forgive my ignorance-
I'm only new to vba.

Many thanks

Steve Flanagan
icemonkeysteve

 

[Tom Ogilvy]

Sub ChangeY_Values()

Dim rng As Range
Set rng = Range("B4:B10").Offset(0, Range("A1").Value)
sRange = "='" & ActiveSheet.Name & "'!" & rng.Address(1, 1, xlR1C1)
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = sRange
End Sub


Worked for me. I used B4:B10 as a base range with other series in the same
rows to the right. I put the number of columns to offset in cell A1.

 

[icemonkeysteve]

Cheers for that Tom- I should be able to use a similar idea for my
project.

Thanks once again!!

Steve