Populating an array with range VBA

Question

I have been given the job of fixing a holiday spreadsheet at work, the problem with the document is that there are direct references, indirect references to other worksheets and some parts non referenced, so if you want to put people in a different order it completely messes the whole thing up. So, what I have been trying to do is to populate an array with the peoples names, sort the array, then cross reference that with the original and find a new order so that this new order can be implemented throughout the worksheets without messing things up.

The problem is that I can't seem to get the arrays to populate, I have looked at the other answers on here, but I'm sure I'm using redim properly and that tends to be the problem.

So Previous is the original listing, Current is the sorted list and Position is the number that relates the previous to the current.

Sub Sorting()

Dim Previous() As Variant
Dim Current() As Variant
Dim maxrow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim Position() As Long
Dim rng As Range
Dim strTemp As String

k = 0
i = 3
maxrow = 3

Do While Worksheets(1).Cells(i, 1).Value <> "STAT.HOL'S (ST)"
maxrow = maxrow + 1
i = i + 1
Loop
maxrow = maxrow - 1

ReDim Previous(0 To maxrow)
ReDim Position(0 To maxrow)
ReDim Current(0 To maxrow)
Previous = Range("a4", Range("a" & maxrow))
Current = Previous
For i = 0 To maxrow
For j = 0 To maxrow
If Current(i) > Current(j) Then
strTemp = Current(i)
Current(i) = Current(j)
Current(j) = strTemp
End If
Next j
Next i

For i = 0 To maxrow
For j = 0 To maxrow

If Previous(i) = Current(j).Value Then
Position(k) = j
k = k + 1
End If

Next j
Next i

End Sub

Thanks for your help. Amy

Does your sorting work? If not, see this [Q+A](http://stackoverflow.com/q/152319/1490783) for other ways. Also, consider indenting your code, it will be much easier to read. — Olle Sjögren, Mar 27 '13 at 12:15
When you assign your array to Previous try adding `.value` to the end. Also within your `Range("a4",)` you don't need to declare the second portion as a separate Range. `Range("a4", "a" & maxrow).value`. — Zaider, Mar 27 '13 at 12:23
No my sorting doesn't work, I wanted to get the first part working beforehand. I do indent my work but this was just a bit of preliminary code. Thanks for the tips, I managed to get it working. — user1545643, Mar 27 '13 at 12:54

score 1 · Answer 1 · answered Mar 27 '13 at 11:56

Excel ranges are always treated as having 2 dimensions even when they are a single column. Also you don't need to redim your variant arrays - just assign the range to a plain variant variable and it will create a variant containing a 2-dimensional array:

Dim Previous as variant
Previous = Range("a4:a" & maxrow)

score 1 · Accepted Answer · answered Mar 27 '13 at 12:20

You do populate the arrays, but you are adressing them the wrong way.

When you assign a range to an array, the array is automatically redimensioned to a two-dimensional array, one for the rows and one for the columns.

Since your range is just one columm, the second dimension is always 1. So after you run the line Previous = Range("a4", Range("a" & maxrow)) you have an array that is dimensioned as:

Previous(1 to 10, 1 To 1)

Also, your For loops could be changed to something like this:

For i = LBound(Current, 1) To UBound(Current, 1)

Next i

Populating an array with range VBA

2 Answers2