标签:VBA
本文的示例整理自vbaexpress.com论坛,供有兴趣的朋友参考。
如下图1所示的工作表,使用Excel的组合功能建立了分级显示。
图1
现在,我想在列A中根据分级显示的层级来插入相应的序号,如下图2所示。
图2
打开VBE,插入一个标准模块,在其中输入下面的代码:
Sub AddLevel()
Dim i As Integer
Dim start_row As Integer
Dim end_row As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer
With Worksheets("Sheet1")
start_row = 3
end_row = .Range("B3").End(xlDown).Row
For i = start_row To end_row
Select Case .Rows(i).OutlineLevel
Case Is = 2
a = a + 1
b = 0: c = 0
Case Is = 3
b = b + 1: c = 0
Case Is = 4
c = c + 1
End Select
.Range("A" & i) = a & IIf(b <> 0, "." & b, "") & IIf(c <> 0, "." & c, "")
Next i
End With
End Sub
运行代码,即可在列A中插入相应的层级序号。