将vlookup结果传递给vba中的数组变量

我在工作表"LutSht"上有一个名为"LUT"的表，如下所示：

A   1   2   3
B   4   5   6
C   7   8   9

在第二个工作表中，"CalSht"单元格A1包含从"LUT"的第一列生成的下拉列表

当我录制一个宏以了解vlookup在VBA中的操作方式时，它会产生：

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],LUT,2,)"

在这种情况下，查找到的值被写入一个单元格。我正试图将它传递给一个数组变量。

这是我有缺陷的代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x(3) As Double
    Dim i As Integer
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        For i = 1 To 3
            x(i)=VLOOKUP(RC[-1],LUT,i+1,) 
'Line kept simple, but myriad of alternatives were tested unsuccessfully 
        Next
    End If
End Sub

到目前为止，我所有纠正语法的尝试都失败了。你能帮忙吗？

假设LUT是一个命名范围：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arrResults() As Variant
    Dim i As Long
    If Not Intersect(Me.Range("A1"), Target) Is Nothing Then
        ReDim arrResults(1 To [COLUMNS(LUT)] - 1)
        For i = 1 To UBound(arrResults)
            arrResults(i) = Evaluate("VLOOKUP(A1,LUT," & i + 1 & ",TRUE)")
        Next i
        'Do something with your array here
        MsgBox Join(arrResults, ", ")
    End If
End Sub

正如Scott所指出的，您还没有初始化Table/ListObject变量。这是未经测试的，但我认为它会起作用：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim loLUT as Excel.ListObject
    Dim x(3) As Double
    Dim i As Integer
    Set loLUT = me.ListObjects("LUT")
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        For i = 1 To 3
            x(i)=VLOOKUP(RC[-1],LUT,i+1,) 
'Line kept simple, but myriad of alternatives were tested unsuccessfully 
        Next
    End If
End Sub

记住使用Option Explicit强制自己声明变量。

相关内容

最新更新

热门标签：