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

 

[Andrew Poulsom]

Sorry, you can't build up a variable name with concatenation like that. Use arrays for bpd and spd.

 

[Showard27]

Many thanks, I did wonder if I was trying to be too clever. I assume therefore I will have to populate each array element individually ?

Simon

 

[Andrew Poulsom]

You could use arrays for bpd and spd. Then your code will work with a slight adjustment.

 

[Showard27]

Sorry Andrew, I am not seeing what you mean, do you suggest a separate 1 row array for each, if so am I not going to have the same issue with concatenating the variable name ?

 

[Andrew Poulsom]

Instead of using bpd1, bpd2 ... bpd12, use an array named bpd with 12 elements (like you have done for spdarray).

 

[Showard27]

Many thanks

Your suggestion triggered an alternative process in my mind, and now poulate the array on the fly as the calculations are completed, rather than collecting them together and populating from a list of variables.

I now have an array (bpd) with 12 entries, populated along the way.

However can you guide me as to how I can use these values to populate "seriescollections(1)" of a chart. The chart already exists, this routine is to update the vales

Thanks

Simon

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim j As Long
    Dim bpd(1 To 12)
    For j = 1 To 12
        bpd(j) = j
    Next j
    ActiveChart.SeriesCollection(1).Values = bpd
End Sub

 

[Showard27]

Andrew

Superb, that has worked a treat.

I am activating the chart with .Activate. Last question, is there a deactivate option ? for post processing.

Thanks for all the help

Simon

 

[Andrew Poulsom]

You can do it without activating, eg:

Worksheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Values = bpd