title: How to store a cell address to variable in VBA to continually change Range limits
body: <p>I am trying to write a macro that searches a whole sheet for a value and then stores the values and locations of ALL hits for that value throughout the sheet. I will do something with the locations and values later, but I need to get this bit working first.</p>
  
  <p>Originally, I used the Range.Find function with iteration and noticed that I was returning the same value. I then tried to have the range being searched change each time a value was found. I would take the address of the previously found value and make it the lower bound of the range. </p>
  
  <p>This worked, to a point, but I ended up getting an infinite loop at the end. The end condition for my loop was when the Range.Find found nothing (since the size of the sheet is always changing and I don't know what the real upper limits will be). What happened was the Range.Find would get stuck on the last value and refuse to move from that spot, regardless of the change I made in the range.</p>
  
  <p>My most recent attempt to deal with this was to also change the <strong>After:=</strong> input to see if that would force the program to move on. It ended up wrecking the process I already had and now I get stuck in an infinite loop with the first value. So, naturally, I just took that part out hoping to make it work again. No luck.</p>
  
  <p>Here's the code:
  [code]</p>
  
  <pre><code>Sub SearchLibrary()
  '
  ' SearchLibrary Macro
  ' Searches MC library for inputed value and returns all related inforamtion 
  in Search sheet
  '
  ' Keyboard Shortcut: Ctrl+s
  '
  
  'Search code to find all matching values and corresponding headers
  
  ' Define variables
  Dim searchn As Integer ' The value input for the search
  If IsNumeric(Sheets("Search").Range("C2")) Then
     searchn = Sheets("Search").Range("C2").Value
  End If
  
  Dim i As Integer ' Simple counter for loops (column number)
  i = 0
  Dim j As Integer 'Simple counter for loops (row number)
  Dim Data As Worksheet ' Define the search area as all of the sheet MC Library
  Worksheets("MC library").Activate
  Set Data = Sheets("MC library")
  Dim loc As Range
  Dim rang As Range
  Dim spce As Range
  Dim mass() As Single
  Dim Found As Variant
  Set rang = Sheets("MC library").Range("C3:Z500")
  Set loc = Sheets("MC library").Range("C3")
  
  On Error Resume Next
  Do
      Set Found = rang.Find(What:=searchn, LookIn:=xlValues, LookAt:= _
          xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False)
      MsgBox (Found)
      Set loc = Sheets("MC library").Range(Found.Address)
      If Found &gt; 0 Then
          ReDim Preserve mass(i)
          mass(i) = Found
          i = i + 1
          Set rang = Sheets("MC library").Range(loc, "Z500")
      End If
  
  Loop Until Found Is Nothing
  
  End Sub
  </code></pre>
  
  <p>[/code]</p>
  
  <p>This is all a work in progress so there's a few things in there that aren't relevant yet. The <strong>Do</strong> loop is where the real problems kick in.</p>
  
  <p>searchn calls in a value from a cell that is an input for the search from the user and is typically a four-digit number. The MsgBox line is simply used for debugging and won't be in the final version.</p>
  
  <p>Any suggestions and help would be greatly appreciated. The biggest issue (I think), is finding a way to store the location of a cell in a variable and then using that to change the range as I go.</p>