|
希望老师能帮忙给解决下
- Sub MyList()
- Dim d1 As Object 'Dic of D-->E 1:N Sheet2
- Dim d2 As Object 'Dic of E-->F 1:N Sheet2
- Dim d3 As Object 'Dic of F-->G 1:N Sheet1
- Dim d4 As Object 'Dic of Date 1:N Sheet1
- Dim arr1 'Sheet1
- Dim arr2 'Sheet2
- Dim arrResult 'Sheet4
- Dim rowN As Long
- Dim rowNR As Long
- Dim arrTmp
- Dim tempS
- Dim Temp
- Dim tempResult
-
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- Set d3 = CreateObject("Scripting.Dictionary")
- Set d4 = CreateObject("Scripting.Dictionary")
- 'Create Dic3
- arr1 = Sheet1.Range("A1").CurrentRegion.Value
- For rowN = 1 To UBound(arr1)
- d3(arr1(rowN, 4)) = arr1(rowN, 5)
- Temp = arr1(rowN, 1) & "-" & arr1(rowN, 2) & "-" & arr1(rowN, 3)
- If d4.exists(Temp) Then
- d4(Temp) = d4(Temp) & vbLf & arr1(rowN, 4)
- Else
- d4(Temp) = arr1(rowN, 4)
- End If
-
- Next rowN
-
- 'Create Dic1,Dic2
- arr2 = Sheet2.Range("A1").CurrentRegion.Value
- For rowN = 1 To UBound(arr2)
- If d1.exists(arr2(rowN, 1)) Then
- d1(arr2(rowN, 1)) = d1(arr2(rowN, 1)) & vbLf & arr2(rowN, 2)
- Else
- d1(arr2(rowN, 1)) = arr2(rowN, 2)
- End If
- If d2.exists(arr2(rowN, 2)) Then
- d2(arr2(rowN, 2)) = d2(arr2(rowN, 2)) & vbLf & arr2(rowN, 1)
- Else
- d2(arr2(rowN, 2)) = arr2(rowN, 1)
- End If
- Next rowN
-
- With Sheet4
- .Range("D:G").ClearContents
- arrResult = .Range("A1:G" & .Range("A1").CurrentRegion.Rows.Count).Value
- For rowN = 1 To UBound(arrResult)
- Temp = d4(arrResult(rowN, 1) & "-" & arrResult(rowN, 2) & "-" & arrResult(rowN, 3))
- If Temp <> "" Then
- arrResult(rowN, 4) = Temp
- 'Get E Column
- tempResult = ""
- For Each tempS In Split(Temp, vbLf)
- If d1.exists(tempS) Then
- tempResult = tempResult & vbLf & d1(tempS)
- End If
- Next tempS
- If tempResult <> "" Then _
- arrResult(rowN, 5) = Right(tempResult, Len(tempResult) - 1)
- 'Get F Column
- Temp = tempResult
- tempResult = ""
- For Each tempS In Split(Temp, vbLf)
- If d2.exists(tempS) Then
- tempResult = tempResult & vbLf & d2(tempS)
- End If
- Next tempS
- If tempResult <> "" Then _
- arrResult(rowN, 6) = Right(tempResult, Len(tempResult) - 1)
- 'Get G Column
- Temp = tempResult
- tempResult = ""
- For Each tempS In Split(Temp, vbLf)
- If d3.exists(tempS) Then
- tempResult = tempResult & vbLf & d3(tempS)
- End If
- Next tempS
- If tempResult <> "" Then _
- arrResult(rowN, 7) = Right(tempResult, Len(tempResult) - 1)
- End If
-
- Next rowN
-
- .Range("A1").Resize(UBound(arrResult), 7).Value = arrResult
- End With
- End Sub
复制代码
|
|