# Is there a limit to the size of a range in vba?

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? Here's the 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 < min And substr1 = substr2 Then min = score best = str ElseIf score = min And substr1 = substr2 Then min = score best = str + " && " + best ElseIf score > min And score <= nextMin And substr1 = substr2 Then nextMin = score nextBest = str min = min best = best End If Next If min > 15 Then bestMatch = "No Match Found" ElseIf min <= 15 Then bestMatch = "1. " + best + " 2. " + nextBest End If End Function
The range limits are the same as the Excel Sheet size limits. If you can put it into a worksheet, then you can map it to a Range. When you say that "it doesn't work", what does that mean?

### 觉得文章有用就打赏一下文章作者

#### 支付宝扫一扫打赏 #### 微信扫一扫打赏 