pass cell values to array

[artz]

Hi,

With the code below, I am trying to pass a range of cell values to an array. In this example, the range is specified as E2:E20. E2 will always be the starting cell, however the ending cell, E20 could be more or less, i.e., the cell range is variable. It would be best to take this into account, however, I don't know how to do that. The code that I tried is below:

Sub pass_range()
Dim i As Integer
Dim Ar As Variant
Ar = Sheets("Sheet1").Range("E2:E20").Value
For i = 2 To 20
Out = Ar(i)
Debug.Print Out
Next i
End Sub

I wanted to confirm that the array was properly passing values from my cell range into the array by viewing the Immediate Window so I added the Debug statement. When I run the code, I receive a 'subscript out of range' error.

Does anyone see why the error occurs and why I don't see the passed cell values in the Immediate Window?

Also, is there a way to modify the code so that the maximum range value can be variable?

Thanks,

Art

 

[pedie]

Do you have sheet name called "Sheet1"?

 

[AlphaFrog]

Sub pass_range()
Dim i As Integer
Dim Ar As Variant
Ar = Sheets("Sheet1").Range("E2:E20").Value
For i = [COLOR="Red"]1 To 19[/COLOR]
Out = Ar([COLOR="Red"]i, 1[/COLOR])
Debug.Print Out
Next i
End Sub

The array index will have two coordinates. One for row and one for column e.g. Ar(row, column)
The array's coordinates will start at 1 irregardless or your range. So the array's column index in this case will always be 1 because your range had only one column (doesn't matter that it was column E).

 

[AlphaFrog]

This will pass the range E2 to the last used cell in column E

Sub pass_range()
Dim i As Integer
Dim Ar As Variant
Dim Lastcell As Long

With Sheets("Sheet1")
Ar = .Range("E2", .Range("E" & Rows.Count).End(xlUp)).Value
End With

For i = 1 To UBound(Ar)
Out = Ar(i, 1)
Debug.Print Out
Next i
End Sub

Here's a very site about Passing And Returning Arrays in VBA
http://www.cpearson.com/excel/PassingAndReturningArrays.htm

 

[artz]

AlphaFrog,

Thanks for your response and code. It does what I needed. Also, thanks for explaining the syntax for how to specify the array output. That's very helpful. As I mentioned, the last row in the range is variable. I tried the following code:

Sub pass_range2()
Dim i As Integer
Dim Ar As Variant
Ar = Sheets("Sheet1").Range("E2:E200").Value
For i = 1 To 199
Out = Ar(i, 1)
Debug.Print Out
Next i
End Sub

What I found is that it passes the blank values after E20 into the array. Is there a change that can be made to the code that will pass a value to the array only if the cell contains a numeric value?

Another possible way would be to stop the loop after the last numeric value. I don't know which is the best practice for coding.

Suggestions?

Thanks,

Art

 

[AlphaFrog]

Did you see my 2nd post (#4) ?

 

[artz]

AlphaFrog,

Apparently, I was writing a response while you were posting that response. Our posts were moments apart.

I checked out your code- that's so great! Thanks. It works the way I expected and needed.

Thanks Again,

Art