VBA 运行时错误 9：下标超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14692298/
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 Runtime Error 9: Subscript out of range
提问by CaryBush
I have been trying to write a small piece of code to validate to confirm whether or not a date is included in an array. I have been able to scroll through the code until I reach the line If lists(i) = TodaysDate Thenwhen the lists(i)show subscript out of range. I have searched through the Internet and I'm unable to resolve this issue.
我一直在尝试编写一小段代码来验证以确认日期是否包含在数组中。我已经能够通过代码，直到我到达行滚动If lists(i) = TodaysDate Then的时候lists(i)表演subscript out of range。我已经通过 Internet 进行了搜索，但无法解决此问题。
My Macro reads as follows:
我的宏如下：
Sub size_an_array()
Dim i As Integer
Dim Range_of_Dates As Integer
Dim TodaysDate As Variant, finish As String
TodaysDate = Range("Sheet11!c2")
ThisWorkbook.Worksheets("Sheet11").Activate
lists = Range("Processed_Dates")
Range_of_Dates = UBound(lists, 1) - LBound(lists, 1) + 1
For c = 1 To UBound(lists, 1) ' First array dimension is rows.
For R = 1 To UBound(lists, 2) ' Second array dimension is columns.
Debug.Print lists(c, R)
Next R
Next c
x = Range_of_Dates 'UBound(lists, 1)
ReDim lists(x, 1)
i = 1
Do Until i = x
If lists(i) = TodaysDate Then
Exit Do
End If
Loop
MsgBox "The date has not been found"
End Sub
I'm relatively new to VBAand I have been trying to use named ranges to pull in the array but I'm completely at my wits end in trying to solve this piece.
我相对较新，VBA并且一直在尝试使用命名范围来拉入数组，但在尝试解决此问题时，我完全不知所措。
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Francis Dean
You have ReDimmed the array listsfrom a one dimensioned array to a two dimensioned array and you are then trying to reference an element using only one dimension in the suspect line (below), which is causing your error.
您已将数组lists从一维数组重新调整为二维数组，然后您尝试仅使用可疑行（下方）中的一维来引用元素，这会导致您的错误。
If lists(i) = TodaysDate Then
If lists(i) = TodaysDate Then
For reference, Run-time error 9: Subscript out of rangemeans you are referencing a non-existent array element.
作为参考，Run-time error 9: Subscript out of range意味着您正在引用一个不存在的数组元素。
回答by Siddharth Rout
I think this is what you are trying?
我认为这就是你正在尝试的？
Sub size_an_array()
Dim i As Integer
Dim TodaysDate As Variant, lists
Dim bFound As Boolean
'~~> Change SomeWorksheet to the relevant sheet
TodaysDate = Sheets("SomeWorksheet").Range("c2")
lists = Sheets("Sheet11").Range("Processed_Dates")
i = 1
Do Until i = UBound(lists)
If lists(i, 1) = TodaysDate Then
bFound = True
Exit Do
End If
i = i + 1
Loop
If bFound = True Then
MsgBox "The date has been found"
Else
MsgBox "The date has not been found"
End If
End Sub
If I understand you correctly then it is much easier to use .Find. If you are interested then have a look at this link.
如果我理解正确，那么使用.Find. 如果您有兴趣，请查看此链接。
