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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,096
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
 

Forum statistics

Threads
1,144,236
Messages
5,723,173
Members
422,479
Latest member
Mr_Confused

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