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

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
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.

Code:
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
I think I just need to learn the LBound and UBound a little better and then I'll understand all of this better.
 

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
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.

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,328
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
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...
Code:
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

Board Regular
Joined
Jan 21, 2016
Messages
114

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,328
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 21, 2016
Messages
114
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,304
Messages
5,527,912
Members
409,792
Latest member
shawnash

This Week's Hot Topics

Top