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
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.
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