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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I think I just need to learn the LBound and UBound a little better and then I'll understand all of this better.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top