title: Is there a limit to the size of a range in vba?
body: <p>I created a user-defined function to show the degree to which companies are matchable.  This function works really well, as long as the range I use as thisRange is less than or close to row 2800. I have a list of 4000 companies and when I try to include those in rows over 2800, the function doesn't work. Can the range only be so big?  Is there any way to fix this problem?</p>
  
  <p>Here's the code:</p>
  
  <pre><code>Function bestMatch(company As String, thisRange As Range) As String
  
  Dim min As Double, nextMin As Double
  Dim cell As Range
  Dim score As Double
  Dim best As String, str As String, nextBest As String
  min = 99999
  nextMin = 99999
  
  For Each cell In thisRange.Cells
      str = cell.Value
      substr1 = Left(str, 1)
      substr2 = Left(company, 1)
      score = Leven(company, str)
      If score &lt; min And substr1 = substr2 Then
          min = score
          best = str
      ElseIf score = min And substr1 = substr2 Then
          min = score
          best = str + " &amp;&amp; " + best
      ElseIf score &gt; min And score &lt;= nextMin And substr1 = substr2 Then
          nextMin = score
          nextBest = str
          min = min
          best = best
      End If
      Next
  
  If min &gt; 15 Then
      bestMatch = "No Match Found"
  ElseIf min &lt;= 15 Then
      bestMatch = "1. " + best + "    2. " + nextBest
  End If
  
  End Function
  </code></pre>