variable array problems

Showard27

Board Regular
Joined
Sep 5, 2005
Messages
155
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, you can't build up a variable name with concatenation like that. Use arrays for bpd and spd.
 

Showard27

Board Regular
Joined
Sep 5, 2005
Messages
155
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could use arrays for bpd and spd. Then your code will work with a slight adjustment.
 

Showard27

Board Regular
Joined
Sep 5, 2005
Messages
155

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Instead of using bpd1, bpd2 ... bpd12, use an array named bpd with 12 elements (like you have done for spdarray).
 

Showard27

Board Regular
Joined
Sep 5, 2005
Messages
155

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Example:

Code:
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

Board Regular
Joined
Sep 5, 2005
Messages
155
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can do it without activating, eg:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,071
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top