excel - 运行时错误 1004:使用 VBA 按缩进级别对 Excel 列表进行分组
问题描述
我目前正在尝试修复 Excel VBA 宏。最终目标是按缩进级别对列表进行分组。
结构与此类似:
第 1 行..第 2 行..第 3 行
Einheit-----A -40
Einheit-----B -20
Einheit-----C -20
Einheit-----D 0
奇怪的是宏似乎一直工作到第 409 行。在第 410 行我得到运行时错误 1004。第 410 行的缩进级别是 9。也许你们有一个想法。
+++ 好的,所以我发现运行时错误在某种程度上与缩进级别一致。它总是出现在缩进级别为 9 的一行之后。 +++
Sub AutoGroupBOM()
'Define Variables
Dim StartCell As Range 'This defines the highest level of assembly, usually 1, and must be the top leftmost cell of concern for outlining, its our starting point for grouping'
Dim StartRow As Integer 'This defines the starting row to beging grouping, based on the row we define from StartCell'
Dim LevelCol As Integer 'This is the column that defines the assembly level we're basing our grouping on'
Dim LastRow As Integer 'This is the last row in the sheet that contains information we're grouping'
Dim CurrentLevel As Integer 'iterative counter'
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False 'Turns off screen updating while running.
'Prompts user to select the starting row. It MUST be the highest level of assembly and also the top left cell of the range you want to group/outline"
Set StartCell = Application.InputBox("Select top left cell for highest assembly level", Type:=8)
StartRow = StartCell.Row
LevelCol = StartCell.Column
LastRow = ActiveSheet.UsedRange.Rows.Count
'Remove any pre-existing outlining on worksheet, or you're gonna have 99 problems and an outline ain't 1
Cells.ClearOutline
'Walk down the bom lines and group items until you reach the end of populated cells in the assembly level column
For i = StartRow To LastRow
Rows(i).Select
Level = Cells(i, LevelCol).IndentLevel
For j = 1 To Level - 1
Selection.Rows.Group
Next j
Next i
Application.ScreenUpdating = True 'Turns on screen updating when done.
End Sub
提前致谢!
解决方案
Sub GroupByIndent()
Dim rng As Range, cell As Range
Dim i As Integer
Set rng = Range("A6:A880")
For Each cell In rng
cell.Select
For i = 1 To cell.IndentLevel
Selection.Rows.Group
Next
Next cell
End Sub
推荐阅读
- twig - RainLab.Blog 帖子列表:列出特定顶级类别下的子类别
- python - AttributeError:“世界”对象没有属性“绘制”
- json - 如何在 Flutter 上使用我的 API 在表日历上显示事件
- apache-spark - 使用 spark 日志写入 spark 应用程序 ID
- flutter-layout - 创建配置文件标题部分
- google-apps-script - 在迭代一组值时导出 PDf
- java - 什么是 -Xdiags:verbose with javac 用于?
- html - 带有 flex 的超级菜单,自动分为 4 列
- pip - 无法使用 pip 安装
- mysql - 根据每组的记录筛选分组依据