时间戳

首页 > 解决方案 > 数组下标超出范围的 VLOOKUP

arrays - 数组下标超出范围的 VLOOKUP

问题描述

我正在尝试做我认为应该是一个简单的公式，但事实证明这很困难。我试图通过 VBA 设置一个等于公式的单元格。这是代码： 

Dim pivotws_month As Worksheet
Dim departmentArray() As Variant
Dim i As Long
Dim x As Long
Dim lrow As Long

lrow = pivotws_month.Cells(Rows.Count, 2).End(xlUp).Row
ReDim departmentArray(1 to lrow)
departmentArray = pivotws_month.Range("B4:B" & lrow)

i = 4  
For x = 1 to UBound(departmentArray)
   pivotws_month.Cells(i,4).Value = pivotws_month.Cells(i,3) / Application.WorksheetFunction.VLookup(departmentArray(x), pivotws_month.Range("G4:H" & lrow), 2, False)
i = i + 1
Next x

我已经调试了我的变量并且我的 lrow 是正确的（297）并且我的数组中的项目数是正确的（294）。我的 For 循环中不断出现下标超出范围的错误，我不知道为什么。请帮忙，我一直在四处寻找答案。

标签： arraysvbaexcelvlookup

解决方案

您已经创建了一个二维数组；提供所有参考文献中的排名。

...

'this Redim is completely unnecessary
'ReDim departmentArray(1 to lrow)

...

with pivotws_month
    i = 4  
    lrow = .Cells(Rows.Count, 2).End(xlUp).Row
    'create a 2-D array of many 'rows' and one 'column
    departmentArray = .Range("B4:B" & lrow).value
    For x = LBound(departmentArray, 1) to UBound(departmentArray, 1)
       .Cells(i,4).Value = .Cells(i,3) / Application.VLookup(departmentArray(x, 1), .Range("G4:H" & lrow), 2, False)
        i = i + 1
    Next x
end with

从工作表范围加载变量数组始终会生成二维数组。无论您是从单行还是从单列加载都没有关系。它还Option Base 0在批量加载代码行之前将任何声明或 ReDim 命令丢弃到一维数组。

您可以在批量加载事件后使用以下内容测试数组的尺寸。

debug.print lbound(departmentArray, 1) & ":" & ubound(departmentArray, 1)
debug.print lbound(departmentArray, 2) & ":" & ubound(departmentArray, 2)

将第一个等级（例如ubound(departmentArray, 1)）视为数组的“行”，将第二个等级（例如ubound(departmentArray, 2)）视为数组的“列”可能会有所帮助。

该 VLOOKUP 没有错误控制。一个简单的不匹配#N/A 将使您的代码陷入困境。Application.Vlookup 的结果可以放入一个变体中，并且可以使用 IsError 测试该变体。可以使用对等于零的变体进行进一步测试来避免#DIV/0！代码中除法运算的结果。

推荐阅读