- Sub 基础表整理()
- On Error Resume Next
- Dim a, c As Range, Rng As Range, RG As Range
- Dim Hx As Long, arr, Brr, d As Object, i&
- With Sheets("产品信息")
- arr = .Range("a2:b" & .Range("a65536").End(xlUp).Row)
- End With
- Set d = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- d(arr(i, 1)) = arr(i, 2)
- Else
- d(arr(i, 1)) = d(arr(i, 1)) & "," & arr(i, 2)
- End If
- Next i
- Brr = Sheets("基础表").Range("d2:d" & Sheets("基础表").Range("d65536").End(xlUp).Row)
- With Columns("C:C")
- .Validation.Delete
- .Font.ColorIndex = 1
- End With
- For i = 1 To UBound(Brr)
- If d.exists(Brr(i, 1)) Then
- If InStr(d(Brr(i, 1)), ",") Then
- With Cells(i + 1, 3).Validation
- .Add 3, 1, 1, d(Brr(i, 1))
- End With
- Cells(i + 1, 3) = Split(d(Brr(i, 1)), ",")(0)
- Cells(i + 1, 3).Font.ColorIndex = 3
- Else
- Cells(i + 1, 3) = d(Brr(i, 1))
- End If
- Else
- Cells(i + 1, 3) = ""
- End If
- Next
- End Sub
复制代码 |