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

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

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

Visit Your Group