VBA数组下标越界，求解

raylenewu

菜鸟一名，求各位大神解释为什么 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

清风竹-

For ar = 1 To UBound(arr) - 2  ‘ 减去2试试。
    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………………


数组arr最大上界值是  UBound(arr)。ar + 2循环到最后，已超过数组数组arr最大上界值。

raylenewu

清风竹- 发表于 2019-10-5 14:37
For ar = 1 To UBound(arr) - 2  ‘ 减去2试试。
    If Cells(ar, 1) = "Title Block" Then
        ka  ...

不知道为什么，For ar = 1 To UBound(arr) - 2  ‘ 减去2，即使加上-2，还是出现下标越界的

raylenewu

清风竹- 发表于 2019-10-5 14:37
For ar = 1 To UBound(arr) - 2  ‘ 减去2试试。
    If Cells(ar, 1) = "Title Block" Then
        ka  ...

好像在sheet0 中，ubound(arr,2)=5,而不是等于66，所以才出现下标越界情况？