一对多查找？（高级）

happytiger2012

1. Public Function FindNB(sheetName As String, findName As String, needFind As String) As Variant
   
2. '这个函数返回一个数组arr
   
3. '.........
   
4. FindNB = arr
   
5. End Function
   
6. 
   
7. Sub test()
   
8. FindNB "Sheet1", "笔", "C"
   
9. End Sub
   

复制代码

根据表名，索引值以及索引值对应的列查找，返回一个数组。
如：FindNB "Sheet1", "笔", "C"
返回一纬数组：Array(1,2,3,4,2,3)

注意数据量很大，大概10万行，谁能给个快捷的方法？

爱疯

Public Function FindNB(sheetName As String, findName As String, needFind As String)
    '这个函数返回一个数组arr
    '.........自行添加防错处理
    Dim A, B(), i&, s&, c%
   
    c = Cells(1, needFind).Column
    A = Sheets(sheetName).Range("a1").CurrentRegion
   
    For i = 2 To UBound(A)
        If A(i, 1) = findName Then
            s = s + 1
            ReDim Preserve B(1 To s)
            B(s) = A(i, c)
        End If
    Next i
   
    FindNB = B
End Function




Sub test()
    Dim arr()
   
    arr = FindNB("Sheet1", "笔", "C")
    Stop
End Sub