arrays - VBA比较不同长度的数组运行时错误9

Question

我有一个 Excel 文件，其中包含表的名称及其对应的列，如下所示：我编写了一个子过程，它返回所有对应的表名称作为列的名称。

我当前的代码执行以下操作：

1. 将 Col A（表名）和 Col B（列名）保存到二维数组
2. 查看数组以查找表的名称是否存在于数组的第一列中。
3. 如果列名存在，则每次在数组中出现该值时都会增加一个计数器并重新确定另一个数组的尺寸。
4. 然后我将该数组设置为等于原始数组并将该表名数组返回到工作表。

我在那里有一个错误处理程序，它说如果保存到名为的数组中的值str_array是 <> Big_Array(i,1)（col A 表名），但用户输入的名称是Col_LookupValue然后Big_Array(i,1)显示以下消息：

"You must enter the name of the Column, NOT the Table."

我的问题是，在将 str_array(r,1) 与嵌套 for 循环中的 Big_array(i,1) 进行比较时，出现“运行时错误 9”下标超出范围错误。

有谁知道这是为什么以及如何解决它。

注意：我参考了这篇文章

提前感谢您的任何想法，建议或答案！！！

这是完整的代码：

Option Base 1
Option Explicit
Option Compare Text
Public Col_LookupValue As String
Sub LookUpColName()

    Dim str_array(), Big_Array(), small_array() As Variant
    Dim r As Long, i As Long, j As Long, Col_LookupValue_Counter As Long
    Dim col_lkupval_found As Boolean


    Application.ScreenUpdating = False
    Application.EnableEvents = False


    'clear previously returned data
     Worksheets("New TRAX").Range("C2:D300").ClearContents

        'Store cols A and B in the Big_Array
        Big_Array = Range("A2").CurrentRegion

                str_array = Array("AUDIT_REQUEST", "EMPLOYEE_CONTROL", "GLOBAL_NAME", "OID$", "PN_INTERCHANGEABLE", "PN_RESTRICTION", "TASK_CARD", "TASK_CARD_ITEM", "TASK_CARD_RESOLUTION_ITEM", "UOM_CONVERSION", "WO")


        'Starting in the second row of the Big_Array loop through each Big_Array
        For i = LBound(Big_Array, 1) To UBound(Big_Array, 1)

            'Note: I used Option Compare Text at the _
            beginning of the module to make the text _
            comparisons non case sensitive
             For r = LBound(str_array, 1) To UBound(str_array, 1)
             If Big_Array(i, 1) <> str_array(r, 1) And Col_LookupValue = Big_Array(i, 1) Then

              MsgBox "You must enter the name of the Column, NOT the Table", vbExclamation, "Error Encountered"

                Exit Sub


            ElseIf Big_Array(i, 2) Like Col_LookupValue Then

                'excecutes below if this is true
                col_lkupval_found = True

                'increase the Col_LookupValue_Counter by 1 each _
                time the Col_LookupValue matches a value in col B
                Col_LookupValue_Counter = Col_LookupValue_Counter + 1

                'Redimension the small_array array with each instance _
                of a the Col_LookupValue in the Big_Array.
                ReDim Preserve _
                small_array(1 To 2, 1 To Col_LookupValue_Counter)
                   'Start a j counter to populate the small_array array; _
                    note, that 2 To 2 subsets the array and only pulls the _
                    SECOND column of it
                    For j = 1 To 1
                    'The small_array array equals the current Big_Array
                          small_array(j, Col_LookupValue_Counter) _
                          = Big_Array(i, j)
                    Next j
            End If
          Next r
        Next i

            'If no 'Like' was found then show the message box
            If col_lkupval_found = False Then
               MsgBox "You must enter the the full name of the Column.", vbExclamation, "Column Name Does Not Exist"
               Exit Sub
            End If


        'Transpose the small_array onto sheet
        ActiveSheet.Range("C2", Range("C2").Offset(Col_LookupValue_Counter - 1, 1)) _
        = Application.Transpose(small_array)

      'Write Col_LookupValue to sheet
      Worksheets("New TRAX").Cells(2, 4).Value2 = Col_LookupValue

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

我也尝试过不使用 str_array() 并且当我只使用 1 个条件时像这样：

If Big_Array(i, 1) <> "Table_Name" And Col_LookupValue = Big_Array(i, 1) Then

该代码完全按预期工作。但是，当我尝试这个时：

If (Big_Array(i, 1) <> "Table_Name1" Or Big_Array(i, 1) <> "Table_Name2" Or Big_Array(i, 1) <> "Table_Name3") And Col_LookupValue = Big_Array(i, 1) Then

即使 Col_LookUpValue=Table_Name1, 2 以此类推，也会引发错误。

标签： arraysexcelvba