帮忙看一段代码！老提示下标越界！

zangma

帮忙看一段代码！老提示下标越界！
请高手赐教
Private Sub CommandButton1_Click()
Dim RangeArray() As String
Dim aaa As String
Dim bbb As String
    aaa = ThisWorkbook.Path & "\"
    bbb = Dir(ThisWorkbook.Path & "\") ', vbDirectory
   ReDim RangeArray(1 To 26)
   i = 1
While i > 26
If bbb <> "111.xls" Then
i = i + 1
RangeArray(i) = "'aaa & [ & bbb & ] & sheets(1)'! & R5C3:R16C6"
End If
bbb = Dir
Wend
Worksheets(1).Range("R5C3").Consolidate Sources:=RangeArray, Function:=xlSum
End Sub

lu_zhao_long

建议你把 i = 1改成 i = 0；
或者，你把 i = i + 1 放到RangeArray(i) = "'aaa & [ & bbb & ] & sheets(1)'! & R5C3:R16C6" 语句的后面。因为当 i = 26并再次进入While --- Wend循环体内以后，会对RangeArray(27)赋值的！

zangma

改后运行时运行到累加的那句时说应用程序定义或对象定义错误

lu_zhao_long

我的理解也有错误！在While -- Wend 循环体内的条件为什么是 i > 26？
Consolidate 函数能不能跨工作薄取数就不得而知了！请看说明：
Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet.