Hi Derek,
Integer does exist and has it's uses.
VBA generally converts integer to long it's true... but not always.
points 2 and 7 seem to say the same thing.
Lisa
Sent: Mon, May 26, 2014 11:47 am
Subject: Re: [ExcelVBA] Subscript out of range
Dear David
There are several things wrong with your code. This is what you should have written :-
Option Explicit ' NEVER work without this as first line. EVER.
Sub Exercise()
Dim nColumn As Long, nRow As Long
Dim r_1(2, 5) As Variant
For nRow = 1 To 2
For nColumn = 1 To 5
r_1(nRow, nColumn) = Range(Cells(nColumn, nRow), Cells(nColumn, nRow)).Value
Next nColumn
Next nRow
End Sub
1. If you had used Option Explicit this would have flagged up earlier errors and forced you to dimension properly.
2. If you had named your variables better it would have been more obvious to you what was going on.
3. The VBA Integer type does not exist as such in modern PCs so use Long.
4. Using the Variant type for the arrays is better as you cannot always know what type of data is in the cells.
5. Dim r_1(1 To 2, 1 To 5) As Variant . . . is more in line with what you are trying to do.
6. And - you don't need the second array as you can refer to the second row of the array with its row index.
7. Don't call it R_1, call it something that makes sense in English.
Finally consider this code :-
Sub LoadTwoDimensionArrayAllAtOnce()
Dim r_1() As Variant
r_1 = Range("A1:E2").Value
End Sub
Regards
Derek Turner
England +++
Hello,
I am working on an exercise where I have 2 sets of numbers in 2 rows A1:E1 and A2:E2.
I am trying to populate these numbers in these into 2 arrays. The arrays are :
Dim r_1(1, 5) As Integer, Dim r_2(1, 5) As Integer
I am using nested For Loops to try and populate the values into the arrays:
For i = 1 To 2
For j = 1 To 5
r_1(row1, j) = Range(Cells(row1, i), Cells(row1, i)).Value
r_2(row2, j) = Range(Cells(row2, i), Cells(row2, i)).Value
Next j
Next i
r_1(row1, j) = Range(Cells(row1, i), Cells(row1, i)).Value
This above is working and getting the right value into the array, but
r_2(row2, j) = Range(Cells(row2, i), Cells(row2, i)).Value
the above fails, with "Subscript out of range". I am not sure what is wrong.
Thanks in advance. Hope everyone's day is going well.
David
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA ----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com ----------------------------------
Tidak ada komentar:
Posting Komentar