Have the Array reference relatively, so I can start at 1 when referencing

[hitbid]

I am trying to learn to build a 2d array, by finding the data and then populating the value of it.

In the below example, I have a used range that starts in Cells(7,7) and continues to Cells(11,8). Very small data set I am using for learning.

When I get all the way to the bottom and use the MsgBox arrtest(7,8), how can I change this to use relative instead. I would like to use arrtest(1,2), or arrtest(1,1), even though this data starts in column 7 row 7.

Thanks.

Sub arrSet()
Dim arrtest() As Variant
Dim firstRow, firstCol, lastRow, lastCol As Integer


Call reset_usedrange


lastRow = ActiveSheet.UsedRange.Rows.Count
lastCol = ActiveSheet.UsedRange.Columns.Count
firstRow = Cells(ActiveCell.Row, ActiveCell.Column).End(xlUp).Row
firstCol = Cells(firstRow, ActiveCell.Column).End(xlToLeft).Column
If ActiveCell.Column - firstCol > 1 Then
firstCol = ActiveCell.Column
End If


ReDim arrtest(firstRow To lastRow + firstRow - 1, firstCol To lastCol + firstCol - 1)


For i = firstRow To lastRow + firstRow - 1
    For j = firstCol To lastCol + firstCol - 1
        arrtest(i, j) = Cells(i, j).Value
    Next j
Next i


[U][B]MsgBox arrtest(7, 8)[/B][/U]


End Sub


Public Sub reset_usedrange()
    a = ActiveSheet.UsedRange.Rows.Count
End Sub

 

[hitbid]

I think I just need to learn the LBound and UBound a little better and then I'll understand all of this better.

 

[hitbid]

I still can't figure out how to write out the very bottom MsgBox statement, because ultimately I want to start at 1, vs the actual cell address, but can't figure out the syntax to write it correctly.

Sub arrSet()
Dim arrtest() As Variant
Dim firstRow, firstCol, lastRow, lastCol, LowerR, LowerC, UpperR, UpperC As Integer
Dim myRange As Range
Set myRange = Selection


firstRow = ActiveCell.Row
firstCol = ActiveCell.Column


lastRow = firstRow + myRange.Rows.Count - 1
lastCol = firstCol + myRange.Columns.Count - 1


ReDim arrtest(firstRow To lastRow, firstCol To lastCol)


For i = LBound(arrtest, 1) To UBound(arrtest, 1)
    For j = LBound(arrtest, 2) To UBound(arrtest, 2)
        arrtest(i, j) = Cells(i, j).Value
    Next j
Next i


LowerR = LBound(arrtest, 1)
UpperR = UBound(arrtest, 1)
LowerC = LBound(arrtest, 2)
UpperC = UBound(arrtest, 2)


MsgBox arrtest(LowerR, LowerC)
MsgBox [B]ActiveCell.Range(Cells(arrtest(1, 1)))[/B]

 

[Rick Rothstein]

If you set the range equal to a Variant variable, the array it creates is automatically "relative" to use your word. So, using your Cells(7,7) to Cells(11,8) and a Variant array variable named ArrTest...

Dim ArrTest As Variant
ArrTest = Range(Cells(7, 7), Cells(11, 8))

I would note that this range can also be written as Range("G7:H11") so that the assignment to ArrTest could have also been written this way...

ArrTest = Range("G7:H11")

At this point, ArrTest(1,1) contains the value in cell G7, ArrTest(1,2) contains the value in cell H7, ArrTest(2,1) contains the value in cell G8 and so on.

 

[hitbid]

Thanks, I see what you mean. But you set it as ArrTest, and not ArrTest(), and my knee jerk there is that it needed the () to be considered an array.

 

[Rick Rothstein]

Thanks, I see what you mean. But you set it as ArrTest, and not ArrTest(), and my knee jerk there is that it needed the () to be considered an array.

Variants can hold anything including an array. Once the variant contains an array, you access the elements of the array as you normally would using the parentheses.

 

[hitbid]

So I do not have to redim the arrtest. I just make it equal the range.

You are right of course, it references relatively, so I can use arrtest(1,1,) even if the range starts in another spot.

Very interesting. Now I am wondering why I was even bothering with the reDim piece. Guess that will come later!