Syntax for setting Excel Chart parameters

[bhujanga]

I'm using a macro to set parameters for several charts that I have based on the shape of the data that gets imported from Access. I'm having trouble figuring out the syntax to recognize non-literal values. So there are basically two things I need t know. Let's say I know that the values for data series #1 start in $A$1, and that my code has ascertained that it is 6 columns wide. If I already knew it was 6 columns wide, I could type in the following code:

ActiveChart.SeriesCollection(1).Values = "='Product Sales'!$A$1:$F$1"

But I don't know how to replace the "F" with the variable (let's say 'x') that stores the number 6.

Secondly, I might have a different number of rows of data series, and will want to execute the above statement for each one. So I will want to increment the 'SeriesCollection' index, i.e. the "(1)" in the statement above. I've tried replacing it with a variable and excel is not pleased with that approach.
Can someone explain how I code these two things?
Thank you.

 

[SkipVought]

hi,

Where ??? is your xvalues range also???

dim i as integer, NumOfRows as integer, NumOfCols as integer

for i = 1 to NumOfRows 
  with ActiveChart.SeriesCollection(i)
    .xvalues = "='Product Sales'!" & range(cells(???, 1), cells(???, NumOfCols)).address

    .Values = "='Product Sales'!" & range(cells(i, 1), cells(i, NumOfCols)).address
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}