View Full Version : Arrays and Accessing Elements by Index

10-12-2007, 02:16 PM
I assume that I have 1 contiguous range starting in A1 that looks like the attached jpeg

and use

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select

to select the range. Once the range is selected I would like to create a 3 by 3 array with the correct dimensions based on the selected range

Then I would like to be able to preform calculations on certain elements of the array. For example add items (1,1)+(2,2)+(3,3). The answer should be 8.6+77.7+0.9=87.1.

I have very limited experience with arrays in vba and do not really know how to access elementes of >1D arrays. Thanks!

10-12-2007, 04:59 PM
The code you posted selects all of the .UsedRange and is equivilant to:


If you want to create an array that has the values in the first three columns of the sheet,
Dim myRange As Range
Dim myArray As Variant
With ActiveSheet
Set myRange = Range(.Range("a1"), .Range("c65536").End(xlUp))
End With
myArray = myRange.Value will do the job. (If you only want the data without the headers use "a2" rather than "a1".)
myArray will be a dimensioned (1 to n, 1 to 3) where n is the number of rows.
I hope this helps.

10-13-2007, 10:08 AM
A minor point, the line:
Set myRange = Range(.Range("a1"), .Range("c65536").End(xlUp)) could, in this case, be reliably replaced with:
Set myRange = Range(.Range("a2"), .Range("C1").End(xlDown)) since you've been explicit about it being a contiguous range. This way, you can afford to have something in column C below the table without upsetting the range.

To do your calculation, this, after mikerickson's code:
myresult = myArray(1, 1) + myArray(2, 2) + myArray(3, 3)
MsgBox myresultwhich rather than 87.1, gives 87.2 - naturally :).

10-14-2007, 01:59 AM
If you set a Range variable to contain your data, you can simply use a Range qualified cell reference to return the values. This example assumes a block of data, top left cell of which is F9

Sub Test()
Dim MyRng As Range, Result As Single
Set MyRng = Range("F9").CurrentRegion
With MyRng
Result = .Cells(2, 1) * .Cells(3, 2) * .Cells(4, 3)
End With
Range("A1") = Result
End Sub

10-15-2007, 06:50 AM
thank you for all your responses they solved my problem