Array challenge

Showard27

Board Regular
Joined
Sep 5, 2005
Messages
155
I am having a problem with an array code, that was working but has now stopped. I am getting a subscipt out of range error when trying to pass an array element to the debug.print code.

The change I have made is to move from a hard coded column (eg "B") to a column defined by an offset.

Here is the section of code that is causing me the challenge and I cant seem to fix it, most of the lines I have put in place to see what is happening.

Variables FirstRow, LastRow and RYear are defined earlier in the code and are being written correctly which I have proven by the select range line I have added to check

Code:
dim pie1 as variant ' Declare the array as dynamic
pie1 = ActiveSheet.Range("A" & FirstRow & ":A" & LastRow).Offset(0, (RYear - 35) + os).Value ' here I fill the array
ActiveSheet.Range("A" & FirstRow & ":A" & LastRow).Offset(0, (RYear - 35) + os).Select ' this is a debug code line to make sure I am selecting the right range on the worksheet
Debug.Print LBound(pie1) ' this shows that the array is working across the range as is returning 1
Debug.Print UBound(pie1) ' this shows that the array is working across the range as for the sample dataset it is returning 4
Debug.Print pie1(UBound(pie1)) ' this is returning a subscript error
I am not sure what is happening and I am sure its pretty simple but I just cant find the problem. Any help greatly appreciated.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,088
Office Version
  1. 2013
Platform
  1. Windows
Hi
Try
VBA Code:
 Debug.Print pie1(UBound(pie1),1)

Or
VBA Code:
pie1 = Application.Transpose(ActiveSheet.Range("A" & FirstRow & ":A" & LastRow).Offset(0, (RYear - 35) + os).Value)
Debug.Print pie1(UBound(pie1))
 
Last edited:

Showard27

Board Regular
Joined
Sep 5, 2005
Messages
155
OK thank you so that worked, but I am not sure its changed as the data and array are only one column wide.

however now that part is debugged, I now need to get the array data which is clearly in the array into a pie chart.

here is my former code, and again its failing as the data seems to be in the array but its not passing to the pie chart

Code:
ActiveSheet.ChartObjects("Pie6").Activate
ActiveChart.FullSeriesCollection(1).Values = pie6
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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