|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub 测试()
- With ThisWorkbook.ActiveSheet
- 数据 = .Range(.[a1], .UsedRange).Columns("A:E").Value
- Set Dic主皮 = CreateObject("scripting.dictionary")
- For 行 = 1 To UBound(数据)
- If 数据(行, 1) Like "主皮*" Then
- 主皮序号 = Replace(数据(行, 1), "主皮", "")
- 主皮序号 = Replace(主皮序号, ":", "")
- If 主皮序号 Like "*-*" Then 主皮序号 = Split(主皮序号, "-")(0) & ""
- 主皮序号 = 主皮序号 & "》"
- Dic主皮(主皮序号) = Dic主皮(主皮序号) & "," & Cells(行 + 1, "E").Address
- If 上一主皮 <> "" Then Dic主皮(上一主皮) = Dic主皮(上一主皮) & ":" & Cells(行 - 1, "E").Address
- 上一主皮 = 主皮序号
- End If
- If 行 = UBound(数据) Then Stop: Dic主皮(主皮序号) = Dic主皮(主皮序号) & ":" & Cells(行, "E").Address
- Next
- For Each Key In Dic主皮.keys
- Dic主皮(Key) = "=sum(" & Mid(Dic主皮(Key), 2) & ")"
- Next
- With Dic主皮
- ThisWorkbook.ActiveSheet.[G4].Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items)) '转置输出
- End With
- End With
- End Sub
复制代码 |
|