在 excel 中将表数组读取为范围是否有限制?

Question

这里的vba 宏中的新手，我正在尝试使用我在下面随附的链接中找到的 vba 代码，但当我的范围很大时它不起作用

我有一个 vba 函数，适用于范围 C16000：C16954，但不适用于范围 C116000：C116954，尽管它们读取的行数相同

FuzzyvLookup(B2,D16000:D16954,1) = "SOME VALUE" 
FuzzyvLookup(B2,D116000:D116954,1) = #N/A when i expect it to be 
FuzzyvLookup(B2,D116000:D116954,1) = "SOME VALUE"

关于为什么会这样的任何建议？

添加了指向我所指的宏代码的链接：(它真的很长，所以我没有把它粘贴到这里！ https://www.mrexcel.com/board/threads/fuzzy-matching-new-version-plus-explanation.195635/

Answer 1

你可以这样做：

Function FuzzyvLookup(myRange As range, strLookup As String, lookupType)
' lookup type
' 1 first
' 2 mid
' 3 last
' 0 all
Dim a() As Long
myRow = 2
myCol = 2
ReDim a(myRow - 1, myCol - 1) As Long
a(0, 0) = 9
a(1, 0) = 6
a(0, 1) = 9
a(1, 1) = 6
Dim myVal
myVal = ""
For b = 1 To myRange.Rows.Count
If lookupType = 1 And myRange.Item(b, 1) Like strLookup & "*?" Then
myVal = myRange.Item(b, 2)
Exit For
End If
If lookupType = 2 And myRange.Item(b, 1) Like "?*" & strLookup & "*?" Then
myVal = myRange.Item(b, 2)
Exit For
End If
If lookupType = 3 And myRange.Item(b, 1) Like "?*" & strLookup Then
myVal = myRange.Item(b, 2)
Exit For
End If
If lookupType = 0 And myRange.Item(b, 1) Like "*" & strLookup & "*" Then
myVal = myRange.Item(b, 2)
Exit For
End If
Next b
FuzzyvLookup= myVal
End Function

在您的单元格中可以测试此宏：

Macro1(D16000:D16954, B2, 1) 

备注：1 表示优先(从字符串的字符 1 开始(