表格数组下标超出范围

I am creating a macro which will select sheets from a larger workbook, move and save those sheets as a new workbook, and then move onto the next set.

我正在创建一个宏,它将从较大的工作簿中选择工作表,将这些工作表移动并保存为新工作簿,然后转到下一组。

I have created a pseudo "array" with start and end values (designated by sheet index numbers).

我创建了一个带有开始和结束值的伪“数组”(由工作表索引号指定)。

I am encountering a "Subscript out of range" error after completing the section that saves the file, but before the loop which would pull the next set of worksheets.

我在完成保存文件的部分之后遇到“下标超出范围”错误,但在循环之前会拉出下一组工作表。

Below is my code. Any help with this error would be appreciated.

以下是我的代码。任何有关此错误的帮助将不胜感激。

Dim Start As Integer
Dim Finish As Integer
Dim SR As Integer  
Dim SC As Integer
Dim ER As Integer
Dim EC As Integer
SR = 2
SC = 5
ER = 2
EC = 6
Start = Sheets("REF").Cells(SR, SC).Value
Finish = Sheets("REF").Cells(ER, EC).Value
Dim sheetArray() As Double
Dim i As Integer
Dim c As Integer
i = 0
c = Start
lastrow = Cells(100, SC).End(xlUp).Row

Do Until SR = lastrow

    Do Until c > Finish
        ReDim Preserve sheetarray (0 to i)
        i = i + 1
        c = c + 1
    Loop

    Sheets(sheetarray).Copy
    ActiveWorkbook.SaveAs Filename:= _ XXXXXXXXXXXXXXXXXX

    C = Start
    i = 0
    SR = SR + 1
    ER = ER + 1
Loop

EDIT: 16:35 Central US

编辑:16:35美国中部

Currently, the relevant code block matches what is above, through the line lastrow = Cells(100, SC).End(xlUp).Row

目前,相关的代码块与上面的内容匹配,通过行lastrow = Cells(100,SC).End(xlUp).Row

Do Until SR = lastrow

直到SR = lastrow

ReDim sheetArray(i)

Do Until c > Finish
    ReDim Preserve sheetArray(i)
    sheetArray(i) = c
    i = i + 1
    c = c + 1

Loop



Sheets(sheetArray).Copy
ActiveWorkbook.SaveAs Filename:= _
    XXXXXXXXXXXXX

c = Start
i = 0
SR = SR + 1
ER = ER + 1

Loop

2 个解决方案

#1

You will need three things here:

你需要三件事:

ReDim the array before you load each sheet index because the way you have it now it will just keep building on each loop and thus you will get the Subscript out of range error starting at the second loop - because the array basically has, as an example, 1 3 5 from first and then 1 3 5 3 7, with 1 3 5 from first then 3 7 from second.

在加载每个工作表索引之前重新调整数组,因为现在它的方式只会继续构建在每个循环上,因此从第二个循环开始,你将得到下标范围错误 - 因为数组基本上有,作为一个例子首先是1 3 5,然后是1 3 5 3 7,首先是1 3 5,第二是3 7。

To set the value of the array each time. You only set the elements of the array

每次设置数组的值。您只需设置数组的元素

Qualify which workbook to copy the sheets from, because each time you copy the sheets it sets the active workbook to the newly copied workbook.

限定从哪个工作簿复制工作表,因为每次复制工作表时,它都会将活动工作簿设置为新复制的工作簿。

Build your Do Loop block like this:

像这样构建你的Do Loop块:

Do Until SR = lastrow

    ReDim sheetArray(0) 'or you can put i here since you set it to zero at the bottom

    Do Until c > Finish

        ReDim Preserve sheetArray(i)
        sheetArray(i) = c

        i = i + 1
        c = c + 1

    Loop

    Workbooks("myWkb").Sheets(sheetArray).Copy 'where myWkb is the workbook name you need ... you can also use ThisWorkbook (meaning the workbook where the code is running) but this is not best practice
    ActiveWorkbook.SaveAs Filename:="XXXXXXXXXXXXXXXXXX"

    c = Start
    i = 0
    SR = SR + 1
    ER = ER + 1

Loop

#2

As I could see it, the problem is that you are just adjusting the dimension of your sheetArray, but you are not putting anything inside. So basically, the values inside the array are all zeros. Then you are asking Excel to copy sheets(0), which is out of range because sheet numbers start at 1.

正如我所看到的,问题是你只是在调整sheetArray的尺寸,但你没有把任何东西放进去。所以基本上,数组中的值都是零。然后,您要求Excel复制工作表(0),这超出了范围,因为工作表编号从1开始。

You can fix this by writing inside your array the indices of the sheets you want to copy:

您可以通过在数组中写入要复制的工作表的索引来解决此问题:

Do Until c > Finish
    ReDim Preserve sheetarray (0 to i)
    sheetarray(i) = c ' <~~~~ or something else, according to your goal
    i = i + 1
    c = c + 1
Loop

p.s.: It is better to make sheetArray an array of Integer (not Double), since its elements are indices of sheets...However, even with doubles it should work if the array's contents are set properly.

p.s。:最好使sheetArray成为一个Integer数组(而不是Double),因为它的元素是工作表的索引......但是,即使有双精度数,它也应该可以正常设置数组的内容。

#1