|
本帖最后由 X.Z. 于 2012-12-12 14:17 编辑
希望达到类似VLOOKUP功能
可以由ALL这张表的 A栏第三码或是前面三码
搜寻LIST这张表D栏.. 对应的B栏名称至M栏
谢谢
- Sub 提取Name()
- Dim arr, i&
- Dim dic As Object
-
- Set dic = CreateObject("scripting.dictionary")
- With Worksheets("List")
- arr = .Range("a1").CurrentRegion
- For i = LBound(arr) + 1 To UBound(arr)
- If Len(arr(i, 4)) > 0 Then dic(Split(arr(i, 4), ".")(2)) = arr(i, 2)
- Next
- End With
-
- With Worksheets("ALL")
- Dim arrb()
- arr = .Range("a1").CurrentRegion
- i = UBound(arr)
- ReDim arrb(1 To i, 1 To 1)
- For i = LBound(arr) + 1 To UBound(arr)
- If Len(arr(i, 1)) > 0 Then arrb(i, 1) = dic(Split(arr(i, 1), ".")(2))
- Next
- Range("m:m").ClearContents
- Range("m1").Resize(UBound(arrb), 1) = arrb
- End With
-
- End Sub
复制代码
|
|