title: Populating an array with range VBA
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>So Previous is the original listing, Current is the sorted list and Position is the number that relates the previous to the current.</p>
  
  <pre><code>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 &lt;&gt; "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" &amp; maxrow))
  Current = Previous
  For i = 0 To maxrow
  For j = 0 To maxrow
  If Current(i) &gt; 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
  </code></pre>
  
  <p>Thanks for your help.
  Amy</p>