- Sub 布Demo()
- Dim arrCer As Variant
- Dim arrData As Variant
- Dim arrResult() As String
- Dim arrDataCounts() As Long
- Dim lMaxDataCounts As Long
- Dim i As Long, j As Long
- ' 读取数据
- arrCer = Range("F1:F27")
- arrData = Range("B3:C4000")
- ' 初始化数组
- ReDim arrDataCounts(LBound(arrCer, 1) To UBound(arrCer, 1))
- ReDim arrResult(LBound(arrCer, 1) To UBound(arrCer, 1), 1 To 1)
- ' 算出结果
- For i = LBound(arrData, 1) To UBound(arrData, 1)
- For j = LBound(arrCer, 1) To UBound(arrCer, 1)
- If arrData(i, 1) = arrCer(j, 1) Then
- ' 列数据计数累加1
- arrDataCounts(j) = arrDataCounts(j) + 1
- ' 必要时扩展结果数组
- If lMaxDataCounts < arrDataCounts(j) Then
- lMaxDataCounts = arrDataCounts(j)
- ReDim Preserve arrResult(LBound(arrResult, 1) To UBound(arrResult, 1), 1 To lMaxDataCounts)
- End If
- ' C列数据写入结果数组
- arrResult(j, arrDataCounts(j)) = arrData(i, 2)
- End If
- Next j
- Next i
- ' 写结果
- Range("H3").Resize(UBound(arrResult, 2), UBound(arrResult, 1)) = Application.WorksheetFunction.Transpose(arrResult)
- End Sub
复制代码
|