vba - 运行时错误“9”：下标超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40395047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba - run time error '9': Subscript out of range
提问by Deve1
I am trying to copy 8 different ranges from 8 different tables in a worksheet. So I came up with this coding below. I used union method to combine these ranges, but it returned a
我正在尝试从工作表中的 8 个不同表中复制 8 个不同的范围。所以我想出了下面的这个编码。我使用 union 方法来组合这些范围，但它返回了一个
run-time error '9': Subscript out of range
运行时错误“9”：下标超出范围
at the following line Set range1 = Sheets("Sheet1").Range("O24").
在下一行Set range1 = Sheets("Sheet1").Range("O24")。
Can anyone tell me where I did wrong? I can't seem to detect where is my mistake.
谁能告诉我我哪里做错了？我似乎无法检测到我的错误在哪里。
Sub ONSHORE()
'Last cell in column
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long
Dim range1 As Range, range2 As Range, range3 As Range, range4 As Range, range5 As Range, range6 As Range, range7 As Range, range8 As Range, multipleRange As Range
Set range1 = Sheets("Sheet1").Range("O24")
Set range2 = Sheets("Sheet1").Range("AA40, AC40")
Set range3 = Sheets("Sheet1").Range("AA56, AC56")
Set range4 = Sheets("Sheet1").Range("AA72, AC72")
Set range5 = Sheets("Sheet1").Range("AA88, AC88")
Set range6 = Sheets("Sheet1").Range("AA104, AC104")
Set range7 = Sheets("Sheet1").Range("AA120, AC120")
Set range8 = Sheets("Sheet1").Range("AA130, AC130")
Set multipleRange = Union(range1, range2, range3, range4, range5, range6, range7, range8)
Set WS = Worksheets("Sheet1")
Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant
Dim i As Integer
'Set source workbook
Set wb = ActiveWorkbook
'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsx", MultiSelect:=True)
If IsArray(vFile) Then
For i = LBound(vFile) To UBound(vFile)
Set wb2 = Workbooks.Open(vFile(i))
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
With WS
Set LastCell = .Cells(.Rows.Count, "D").End(xlUp)
LastCellRowNumber = LastCell.Row + 1
End With
'Set selectedworkbook
Set wb2 = ActiveWorkbook
'Select cells to copy
wb2.Worksheets("Sheet1").Range(multipleRange).Copy
'Go back to original workbook you want to paste into
wb.Activate
'Paste starting at the last empty row
wb.Worksheets("Sheet1").Range("D" & LastCellRowNumber).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Close and save the workbook you copied from
wb2.Save
wb2.Close
Next i
Application.ScreenUpdating = True
Application.CutCopyMode = False
End If
End Sub
回答by
I'm guessing that Sheets("Sheet1")does not exist. It could have a space in it Sheets("Sheet 1").
我猜这Sheets("Sheet1")不存在。它可以有一个空间Sheets("Sheet 1")。
There is no reason to union the ranges. The code below is analogous to your union.
没有理由联合这些范围。下面的代码类似于您的工会。
Set multipleRange = Sheets("Sheet1").Range("O24, AA40, AA56, AA72, AA88, AA104, AC120, AA130 ")
设置multipleRange = Sheets("Sheet1").Range("O24, AA40, AA56, AA72, AA88, AA104, AC120, AA130 ")
回答by Mukul Varshney
I got the error 9 when sheet1 was missing while error 1004 when sheet1 is there with no data in range. So the answer for your problem is sheet1 missing.
我在 sheet1 丢失时收到错误 9，而在 sheet1 存在且范围内没有数据时收到错误 1004。所以你的问题的答案是 sheet1 丢失。
