VBA运行时错误9：下标超出范围

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 Then when the lists(i) show subscript out of range. I have searched through the Internet and I'm unable to resolve this issue.

我一直在尝试编写一小段代码来验证以确认数组中是否包含日期。我已经能够滚动代码，直到我到达行如果列表（i）= TodaysDate然后当列表（i）显示下标超出范围。我在互联网上搜索过，我无法解决这个问题。

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 VBA and 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.

任何帮助将不胜感激。

2 个解决方案

#1

You have ReDimmed the array lists from 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.

您已将数组列表从一维数组重新调整为二维数组，然后您尝试在可疑行（下方）中仅使用一个维度引用元素，这会导致您的错误。

If lists(i) = TodaysDate Then

如果列表（i）= TodaysDate那么

For reference, Run-time error 9: Subscript out of range means you are referencing a non-existent array element.

作为参考，运行时错误9：下标超出范围意味着您正在引用不存在的数组元素。

#2

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.

如果我理解正确，那么使用它会更容易。如果您有兴趣，请查看此链接。

#1