EXCEL FEED: Re: [ExcelVBA] Subscript out of range

Selasa, 27 Mei 2014

Re: [ExcelVBA] Subscript out of range

Thanks Derek. David

On Monday, May 26, 2014 2:47 AM, "Derek Turner g4swy@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

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: David <who1012000@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

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

----------------------------------

Visit Your Group