Named range into array using VBA - Subscript out of range

On Sheet1 I have defined a named range called XValues consisting of cells A2 through to A11. I am trying to pass the values of the named range to a 1-dimensional array in VBA in order to do a calculation on them.

The code below is giving me a "subscript out of range" error on the line that puts the calculation results in column E. I watched the expression MyXArray, and the values from the named range do end up in the array, however, a second dimension is added. The value in cell A3 ends up in MyXArray(2,1) for example, where I assumed it would end up in MyXArray(2).

Why? What am I doing wrong?? Why does Excel turn the 1D array into a 2D array..?

Please Login or Register  to view this content.

Is like jindon, he did say it correct. Excel has always ranges as 2 dimensional arrays. You have then got for your range a 1 column 2 D array

If you want to have a 1 D array, then convert it is OK to do..

Please Login or Register  to view this content.

Brian Tuttle
Rule Britannia
God Save the Queen’s Shed

Ref
https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376

Splendid! Exactly what I needed. Thanks!!

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.