variable array problems

[Showard27]

Dear all

This is excel 2010 for info

I am trying to populate an array with a series of variables I have calculated elsewhere in the code.

I am populating 2 rows of 12, using an incremental integer to move through the columns (j)

however I seem to have a problem using the same integer (j) to call the next variable in the sequence, have I got something wrong. The variables ate bpd1, bpd2,......bpd12

Dim spdarray(1 To 2, 1 To 12)
For j = 1 To 12
spdarray(1, j) = bpd & j
spdarray(2, j) = spd & j
Next j

I am sure it is somthing simple, but I can't see it, any help greatly appreciated.

Simon

 

[Showard27]

Andrew

Really appreciate your help on this, I am so close now however:

This works:

Sheets("Main Dash").ChartObjects("SPDchart").Activate
ActiveChart.SeriesCollection(2).Values = spdarray

This does not:

Sheets("Main Dash").ChartObjects("SPDchart").SeriesCollection(2).Values = spdarray

I get a error 438, property or method not supported alert. Is this an excel 2010 foibal, or have I done somthing stupid.

Thanks again

Simon

 

[Andrew Poulsom]

It's:

Sheets("Main Dash").ChartObjects("SPDchart").Chart.SeriesCollection(2).Values = spdarray

 

[Showard27]

Andrew

Thanks again - got it, missed the second declaration of what I am addressing. oops

Simon

 

[mikejvir]

I have a similiar question dealing with the Seriescollection. I have data in a specific worksheet and am looping to add traces (series).

   For iloop = 1 To nfc
       
       clabel = dtab & "!R" & Format(afc(iloop), "0") & "C2"
       cyaxis = dtab & "!R" & Format(afc(iloop), "0") & "C3:R" & Format(afc(iloop), "0") & "C" & Format(icol - 1 + Nlines, "0")
       cxaxis = dtab & "!R" & Format(23, "0") & "C3:R" & Format(23, "0") & "C" & Format(icol - 1 + Nlines, "0")
       
       ActiveChart.SeriesCollection.NewSeries
       ActiveChart.SeriesCollection(iloop).XValues = cxaxis
       ActiveChart.SeriesCollection(iloop).Values = Range(cyaxis)
       ActiveChart.SeriesCollection(iloop).Name = clabel
    
    Next iloop

On the first pass (Iloop = 1)

clabel = "Data_IL!R84C2"
cxaxis = "Data_IL!R23C3:R23C33"
cyaxis = "Data_IL!R84C3:R84C33"

It does not matter how I write the .Values line, the macro stops with Run-time error '1004": Method 'Range' of object '_Global' failed

If I change it to .Values = cyaxis then it sstop with the error Run-time error '91': Object variable or With block variable not set.

Thanks in advance for help me.

Mike Virostko

 

[mikejvir]

PS - If there is a better way to do this. I am all ears.

Mike Virostko

 

[Andrew Poulsom]

This worked for me:

For iloop = 1 To nfc
       
       clabel = "=" & dtab & "!R" & Format(afc(iloop), "0") & "C2"
       cyaxis = "=" & dtab & "!R" & Format(afc(iloop), "0") & "C3:R" & Format(afc(iloop), "0") & "C" & Format(icol - 1 + NLines, "0")
       cxaxis = "=" & dtab & "!R" & Format(23, "0") & "C3:R" & Format(23, "0") & "C" & Format(icol - 1 + NLines, "0")
       
       ActiveChart.SeriesCollection.NewSeries
       ActiveChart.SeriesCollection(iloop).XValues = cxaxis
       ActiveChart.SeriesCollection(iloop).Values = cyaxis
       ActiveChart.SeriesCollection(iloop).Name = clabel
    
    Next iloop

Tip: Use the macro recorder to get the right syntax.

 

[mikejvir]

Thanks Andrew. I appreciate the help.

Mike Virostko