|
参与一下
Sub test()
Dim vArr, brr, crr, dic As Object, sht As Worksheet
Dim i&, j&, num&, str, m&
Set dic = CreateObject("scripting.dictionary")
ReDim brr(1 To 10000, 1 To 3)
For Each sht In Sheets
If sht.Name <> "Sheet1" Then
vArr = sht.Range("A1").CurrentRegion
For i = 2 To UBound(vArr)
For j = 1 To UBound(vArr, 2)
str = Trim(vArr(i, j))
If Len(str) Then
If Not dic.exists(str) Then
num = num + 1
dic(str) = num
End If
brr(dic(str), 1) = vArr(i, j)
brr(dic(str), 2) = brr(dic(str), 2) + 1
brr(dic(str), 3) = brr(dic(str), 3) & "/" & sht.Name & "!" & sht.Cells(i, j).Address(0, 0)
End If
Next j
Next i
End If
Next sht
ReDim vArr(1 To 10000, 1 To 2)
Sheet1.UsedRange.Offset(1).Clear
For i = 1 To UBound(brr)
If brr(i, 2) > 1 Then
m = m + 1
vArr(m, 1) = brr(i, 1)
vArr(m, 2) = brr(i, 2)
crr = Split(brr(i, 3), "/")
For j = 1 To UBound(crr)
Sheet1.Hyperlinks.Add anchor:=Sheet1.Cells(m + 1, j + 2), Address:="", SubAddress:=crr(j), TextToDisplay:=crr(j)
Next j
End If
Next i
Sheet1.Range("A2").Resize(UBound(vArr), 2) = vArr
MsgBox "OK"
End Sub
|
评分
-
查看全部评分
|