Arrays and Accessing Elements by Index

[MachaMacha]

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!

[mikerickson]

The code you posted selects all of the .UsedRange and is equivilant to:

ActiveSheet.UsedRange.Select

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.

[p45cal]

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 :).

[mdmackillop]

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

[MachaMacha]

thank you for all your responses they solved my problem