vba - 应用程序定义或对象定义的错误 Ubound

Question

我有一个 Excel VBA 代码，可以按月从外部文件中检索其记录，并根据列标题进行设置。

但是，我在 .Range("A6").Resize(n, 23) = b 行中遇到应用程序定义或对象定义错误

谁知道为什么

代码:

Sub zz()
Dim arr, c, b(), n&
Application.ScreenUpdating = False
Worksheets("Sheet2").Range("A6").AutoFilter
Workbooks.Open "C:\Users\sophia.tan\Desktop\MasterPlanData.xlsx", 0, 1
arr = Sheets("Excel").UsedRange
ActiveWorkbook.Close 0
c = Array(0, 2, 13, 14, 7, 8, 11, 1, 9, 10, 16, 17, 20, 22, 15, 30, 27, 28, 29, 3, 4, 30)
d = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
ReDim b(1 To UBound(arr), 1 To 23)

For i = 2 To UBound(arr)
    If arr(i, 12) >= DateSerial(Year:=2017, Month:=11, Day:=1) And arr(i, 12) <= DateSerial(Year:=2017, Month:=11, Day:=31) Then
        n = n + 1
        For j = 1 To UBound(c)
            b(n, d(j)) = arr(i, c(j))
        Next
    End If
Next


With Worksheets("Sheet2")

    .Range("A6:T" & Rows.Count).CurrentRegion.AutoFilter field:=1, Criteria1:="<>OFM"
    .Range("A6:T" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=13, Criteria1:="<>Collar & Cuff"
    .Range("A6:T" & Rows.Count).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .Range("A6").Resize(n, 23) = b
    .Range("A6").CurrentRegion.Sort key1:=Range("G6"), order1:=xlAscending, Header:=xlYes
    .Range("A6").Select


End With

Application.ScreenUpdating = 1

End Sub

Answer 1

您对 n 的确定取决于 If 语句。但是，b 的“行”中任何未填充的值都将是 vbnullstring，并且会生成真正的空白单元格。

.Range("A6").Resize(ubound(b, 1), ubound(b, 2)) = b

或者，

For i = 2 To UBound(arr)
    If arr(i, 12) >= DateSerial(Year:=2017, Month:=11, Day:=1) And arr(i, 12) <= DateSerial(Year:=2017, Month:=11, Day:=31) Then
        n = n + 1
        For j = 1 To UBound(c)
            b(n, d(j)) = arr(i, c(j))
        Next
    End If
Next
b = application.transpose(b)
redim preserve b(lbound(b, 1) to ubound(b, 1), lbound(b, 2) to n)
b = application.transpose(b)
.Range("A6").Resize(n, 23) = b

当使用preserve参数时，您只能使用ReDim调整数组的最后一个等级。