|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 按钮1_Click()
Set d = CreateObject("scripting.dictionary")
Set dd = CreateObject("scripting.dictionary")
arr = Sheets(1).[a1].CurrentRegion
Sheets(2).Select
Sheets(2).UsedRange.Offset(1).ClearContents
Application.ScreenUpdating = False
For i = 2 To UBound(arr, 2)
For j = 5 To 10
If Len(arr(j, i)) > 0 Then
dd(arr(3, i) & "#@$" & arr(j, 1)) = 1
d(arr(3, i) & "#@$" & arr(j, 1) & "#@$" & arr(4, i)) = arr(j, i)
End If
Next j
Next i
[b2].Resize(dd.Count) = WorksheetFunction.Transpose(dd.keys)
For i = 2 To UBound(arr, 2)
For j = 14 To 19
If Len(arr(j, i)) > 0 Then
dd(arr(12, i) & "#@$" & arr(j, 1)) = 1
d(arr(12, i) & "#@$" & arr(j, 1) & "#@$" & arr(13, i)) = arr(j, i)
End If
Next j
Next i
Cells(Rows.Count, 2).End(3).Offset(1).Resize(dd.Count) = WorksheetFunction.Transpose(dd.keys)
brr = [a1].CurrentRegion
For j = 2 To UBound(brr)
brr(j, 1) = arr(1, 1)
If d.exists(brr(j, 2) & "#@$" & brr(1, 4)) Then
brr(j, 4) = d(brr(j, 2) & "#@$" & brr(1, 4))
End If
If d.exists(brr(j, 2) & "#@$" & brr(1, 5)) Then
brr(j, 5) = d(brr(j, 2) & "#@$" & brr(1, 5))
End If
crr = Split(brr(j, 2), "#@$")
brr(j, 2) = crr(0)
brr(j, 3) = crr(1)
Next j
[a1].CurrentRegion = brr
Application.ScreenUpdating = True
End Sub
|
评分
-
1
查看全部评分
-
|