Hi,
I have a simple question regarding arrays.
According to the MSDN help, the first element of a 2D array is located at (0,0) MSDN Link
https://msdn.microsoft.com/en-us/library/02e7z943.aspx
But, in my code, if I do the following:
1) Why is MyArr(0,0) is not returning 11 when putting a range into an array?
2) Why is MyArrP(0,0) returning 9900 unlike question 1?
3) How can I avoid the error 9 when redimensioning and preserving the value of MyArrP?
Thank you in advance for your insights because I am getting lost in the array usage...
Shiro
I have a simple question regarding arrays.
According to the MSDN help, the first element of a 2D array is located at (0,0) MSDN Link
https://msdn.microsoft.com/en-us/library/02e7z943.aspx
But, in my code, if I do the following:
Code:
Sub test2()
'Table with Header-A in Cell(10,10)
'
'Header-A Header-B Header-C Header-D
'11 12 13 14
'21 22 23 24
'31 32 33 34
'41 42 43 44
Dim rng As Range, rngout As Range
Dim MyArr As Variant
Dim MyArrP As Variant 'I want to have at the begining an Array of 2 column 1 row to start
Dim MyArrPsize As Integer
ReDim MyArrP(1, 2)
Set rng = Range(Cells(11, 10), Cells(14, 13))
MyArr = rng
Set rngout = Range(Cells(20, 10), Cells(24, 13))
MyArrP(0, 0) = 9900
MyArrP(0, 1) = 9901
MyArrP(0, 2) = 9902
MyArrP(1, 0) = 9910
MyArrP(1, 1) = 9911
MyArrP(1, 2) = 9912
'MyArrP(2, 0) = 9920 '<= error 9
'MyArrP(2, 1) = 9921 '<= error 9
'MyArrP(2, 2) = 9922 '<= error 9
rngout = MyArrP
'MsgBox MyArr(0, 0) '<== why is it returning an error? shouldn t it return 11
MyArrsize = UBound(MyArr, 1) - LBound(MyArr, 1) + 1
MyArrPsize = UBound(MyArrP, 1) - LBound(MyArrP, 1) + 1
MsgBox "MyArrSize = " & MyArrsize & vbNewLine & "LBound(MyArr, 1) = " & LBound(MyArr, 1) & vbNewLine & "UBound(MyArr, 1) = " & UBound(MyArr, 1) & vbNewLine & vbNewLine & "MyArrPSize = " & MyArrPsize & vbNewLine & "LBound(MyArrP, 1) = " & LBound(MyArrP, 1) & vbNewLine & "UBound(MyArrP, 1) = " & UBound(MyArrP, 1)
ReDim Preserve MyArrP(MyArrPsize + 1, 2) 'I want to redim and preserve the value and extend the MyArrP to 2 columns and 2 rows but get error 9
End Sub1) Why is MyArr(0,0) is not returning 11 when putting a range into an array?
2) Why is MyArrP(0,0) returning 9900 unlike question 1?
3) How can I avoid the error 9 when redimensioning and preserving the value of MyArrP?
Thank you in advance for your insights because I am getting lost in the array usage...
Shiro
