|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Option Explicit
Option Compare Text
Sub test()
Dim strFileName$, strPath$, strName$
Dim ar, br(), vResult, i&, j&, r&, k&
Application.ScreenUpdating = False
ar = [A1].CurrentRegion.Value
ReDim vResult(1 To 10 ^ 3, 1)
strPath = ThisWorkbook.Path & "\"
strFileName = Dir(strPath & "*.xlsb")
Do Until strFileName = ""
With GetObject(strPath & strFileName)
r = r + 1
ReDim Preserve br(1 To 2, 1 To r)
br(1, r) = Split(.Name, ".xlsb")(0)
br(2, r) = .Sheets(1).[A1].CurrentRegion.Value
.Close False
End With
strFileName = Dir
Loop
r = 0
For i = 1 To UBound(ar)
For j = 1 To UBound(br, 2)
For k = 1 To UBound(br(2, j), 2)
If br(2, j)(ar(i, 1), k) = ar(i, 2) Then
r = r + 1
vResult(r, 0) = br(1, j)
vResult(r, 1) = ColumnLetter2(k)
End If
Next k
Next j
Next i
[A4].CurrentRegion.Clear
[A4].Resize(r, 2) = vResult
Application.ScreenUpdating = True
Beep
End Sub
Public Function ColumnLetter2(Column As Long) As String
If Column < 1 Then Exit Function
ColumnLetter2 = ColumnLetter2(Int((Column - 1) / 26)) & Chr(((Column - 1) Mod 26) + Asc("A"))
End Function
|
|