|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 raylenewu 于 2019-10-4 23:07 编辑
菜鸟一名,求各位大神解释为什么 arr1(ka, j) = arr(ar + 2, j) ,说是 arr(ar + 2, j) 这个下标越界了。要怎样修改。感觉红色部分都越界了,求解
Sub macro1()
Dim arr, ar%, maxrow%, maxcola%, ka%, j%, arr1(), a%, ra
Dim b%, maxcolb%, rb, kb%, brr1(), jb%
Dim c%, maxcolc%, rc, kc%, crr1(), jc%
Worksheets("Sheet0").Activate
maxrow = Cells(Rows.Count, 1).End(3).Row
arr = Range("a1").CurrentRegion.Resize(maxrow)
a = Range("a:a").Find("Title Block").Row 'find out Title Block row
b = Range("a:a").Find("Common").Row
c = Range("a:a").Find("Page Three").Row
maxcola = Cells(a + 1, Columns.Count).End(xlToLeft).Column 'find Part Category column
maxcolb = Cells(b + 1, Columns.Count).End(xlToLeft).Column
maxcolc = Cells(c + 1, Columns.Count).End(xlToLeft).Column
ra = Cells(a + 1, 1).Resize(, maxcola) 'take line:Part Category as array
rb = Cells(b + 1, 1).Resize(, maxcolb)
rc = Cells(c + 1, 1).Resize(, maxcolc)
ReDim arr1(1 To UBound(arr), 1 To maxcola)
ReDim brr1(1 To UBound(arr), 1 To maxcolb)
ReDim crr1(1 To UBound(arr), 1 To maxcolc)
For ar = 1 To UBound(arr)
If Cells(ar, 1) = "Title Block" Then
ka = ka + 1 'count title block
For j = 1 To maxcola
arr1(ka, j) = arr(ar + 2, j)
Next
ElseIf Cells(ar, 1) = "Common" Then
kb = kb + 1
For jb = 1 To maxcolb
brr1(kb, jb) = arr(ar + 2, jb)
Next
ElseIf Cells(ar, 1) = "Page Three" Then
kc = kc + 1
For jc = 1 To maxcolc
crr1(kc, jc) = arr(ar + 2, jc)
Next
End If
Next
Worksheets.Add(after:=Sheets("Sheet0")).Name = "Sheet1"
Range("a1").Resize(, maxcola) = ra 'add title block in line1
Range("a2").Resize(ka, maxcola) = arr1 'add title block content
Range("a1").Offset(, maxcola).Resize(, maxcolb) = rb
Range("a1").Offset(1, maxcola).Resize(kb, maxcolb) = brr1
Range("a1").Offset(, maxcola + maxcolb).Resize(, maxcolc) = rc
Range("a1").Offset(1, maxcola + maxcolb).Resize(kc, maxcolc) = crr1
With Range("a1").CurrentRegion
.Columns.AutoFit
.HorizontalAlignment = xlLeft
End With
Range("a1").Select
End Sub
|
|