title: Excel VBA Find Values through while loop, store in array, and pass to different sub
body: <p>First off, I appreciate any help anyone can offer. I am writing a macro that will give the user a form to input a number key. The form will search a spreadsheet for the key and return the corresponding name attached to that key. The data may have multiple names per key and it will vary depending on the key. I want to loop through the data with .Find and .FindNext, and find all the possible names attached to that key (i have accomplished this part). The part I am having trouble with is during the loop, storing each name in an array that I can pass to another sub. I want to pass the array so that the user can click another command button and cycle through the possible names before choosing one. </p>
  
  <pre><code>Private Sub FindNameButton_Click()
  Dim KeyMatch As Long
  Dim NameRow As Long
  FindName As Range
  KeyMatch = KeyBox.Value ' The UserForm input box
  
  With Worksheets("Master List"). Range("D:D")
  Set FindName = .Find(What:= KeyMatch, LookAt:= xlWhole, LookIn:= xlValues,           MatchCase:= False)
  If not FindName Is Nothing Then 
  FirstAddress = FindName.Address
  Do
  Application.GoTo FindName
  NameRow = ActiveCell.Row
  Cells(NameRow, 2).Select 'Selects the name associated with the key identifier
  NameBox.Value = ActiveCell.Value 'Fills the UserForm box with the name
  ' I would like to fill the array here with each name is it passes through but I have   no idea how
  
  NameArray(i) = ActiveCell.Value ' ??????
  
  Set FindName = .FindNext(FindName)
  Loop While FindName is Nothing and FristAddress &lt;&gt; FindName.Address
  End With
  End Sub
  
  Private Sub NextNameButton_Click()
  Static cnt As Long
  If cnt &lt;= Ubound(NameArray) Then
  NameBox.Value = NameArray(cnt) 'Fill UserForm Name Box with name from Name Array
  
  Else
  cnt = 0
  End If
  
  cnt = cnt + 1 ' increase every time button is clicked
  End Sub
  </code></pre>