Senin, 26 Mei 2014

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




From: "David who1012000@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: "excelvba@yahoogroups.com" <excelvba@yahoogroups.com>
Sent: Sunday, 25 May 2014, 20:52
Subject: [ExcelVBA] Subscript out of range

 
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



__._,_.___

Posted by: Derek Turner <g4swy@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
----------------------------------
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