|
我的很复杂,没4楼简单,主要是怕不停去增加的时候产生遗漏
- Sub 父子查找()
- Dim vData As Variant, nRow As Double, oDic As Object, oData As Object
- Dim sItem As String, sRM As String, vItem As Variant, vRM As Variant, vTmpRM As Variant
-
- Set oDic = CreateObject("Scripting.Dictionary")
- Set oData = CreateObject("Scripting.Dictionary")
- vData = Sheet2.UsedRange.Value
- For nRow = 2 To UBound(vData)
- sItem = Trim(vData(nRow, 1))
- sRM = Trim(vData(nRow, 2))
- If sItem <> "" And sRM <> "" Then
- If UCase(sRM) Like "Z*" Then
- If Not oData.Exists(sItem) Then Set oData(sItem) = CreateObject("Scripting.Dictionary")
- oData(sItem)(sRM) = 0
- Else
- If Not oDic.Exists(sItem) Then Set oDic(sItem) = CreateObject("Scripting.Dictionary")
- oDic(sItem)(sRM) = 0
- End If
- End If
- Next
- For Each vItem In oData.Keys
- Do While oData(vItem).Count > 0
- vTmpRM = oData(vItem).Keys()(0)
- oData(vItem).Remove vTmpRM
- If oDic.Exists(vTmpRM) Then
- For Each vRM In oDic(vTmpRM).Keys
- oDic(vItem)(vRM) = 0
- Next
- If oData.Exists(vTmpRM) Then
- For Each vRM In oData(vTmpRM).Keys
- oData(vItem)(vRM) = 0
- Next
- End If
- Else '如果不存在如何处置
- oDic(vItem)(vTmpRM) = 0
- End If
- Loop
- oData.Remove vItem
- Next
-
- oData.RemoveAll
- vData = Sheet1.UsedRange.Value
- For nRow = 2 To UBound(vData)
- sItem = Trim(vData(nRow, 1))
- sRM = Trim(vData(nRow, 2))
- If sItem <> "" And sRM <> "" Then
- If Not oData.Exists(sItem) Then Set oData(sItem) = CreateObject("Scripting.Dictionary")
- If UCase(sRM) Like "Z*" And oDic.Exists(sRM) Then
- For Each vRM In oDic(sRM).Keys
- oData(sItem)(vRM) = 0
- Next
- Else
- oData(sItem)(sRM) = 0
- End If
- End If
- Next
-
- vData = Empty
- ReDim vData(1 To 2, 1 To 1)
- nRow = 0
- For Each vItem In oData.Keys
- For Each vRM In oData(vItem).Keys
- nRow = nRow + 1
- ReDim Preserve vData(1 To 2, 1 To nRow)
- vData(1, nRow) = vItem
- vData(2, nRow) = vRM
- Next
- Next
- With Sheet3
- .[D:E].ClearContents
- If nRow > 0 Then [D2:E2].Resize(nRow) = Application.WorksheetFunction.Transpose(vData)
- End With
- End Sub
复制代码 |
|