如何将 vlookup VBA 中的lookup_value写为范围

Question

我是 VBA 的新手 - 我正在尝试在 Excel 中编写一个 Vlookup 函数，以将lookup_value称为一系列值。我已经将我的值存储在一个名为 lookup_range 的范围内，但是当我将 vlookup 值输出到单元格时，我得到 N/A。

我已经在互联网上搜索了所有内容，但是我没有看到任何针对我的问题的解决方案。

Sub Vlookup_values()
    Dim Vlkp_cell, n As Double
    Dim Search_range, row_end, col_end As Double
    Sheets("Movement").Activate
    Range("A1").End(xlDown).Select
    Vlkp_cell = ActiveCell.Row
    ReDim Lookup_range(Vlkp_cell) As String
    n = 0
    Range("A1").Select
    For n = 1 To Vlkp_cell
        ActiveCell.Offset(1, 0).Select
        Lookup_range(n) = ActiveCell.Value
        ActiveCell.Offset(0, 9).Value = Lookup_range(n)
    Next n
    Sheets("Mapping").Activate
    Range("A1").End(xlDown).Select
    row_end = ActiveCell.Row
    Range("A1").End(xlToRight).Select
    col_end = ActiveCell.Column
    Range(Cells(1, 1), Cells(row_end, col_end)).Name = "Search_range"
    Range("Search_Range").Select
    Sheets("Movement").Activate
    Range("A1").Select
    ActiveCell.Offset(0, 1).Select
    n = 0
    For n = 1 To Vlkp_cell
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Application.VLookup(Lookup_range(n), Search_range, 2, False)
    Next n

End Sub

Answer 1

如果我能够理解您试图从代码中实现的目标，请尝试以下代码：

Option Explicit
Sub Vlookup_values()
    Dim Vlkp_cell As Long, n As Long
    Dim Search_range As Range, row_end As Long, col_end As Long
    With Sheets("Mapping")
        row_end = .Range("A1").End(xlDown).Row
        col_end = .Range("A1").End(xlToRight).Column
        Set Search_range = .Range(.Cells(1, 1), .Cells(row_end, col_end)) ' <-- set the Range for the VLookup
    End With
    With Sheets("Movement")
        Vlkp_cell = .Range("A1").End(xlDown).Row
        For n = 1 To Vlkp_cell
            .Range("A" & n).Offset(0, 9).Value = .Range("A" & n).Value ' <-- not sure why you need the values copied 9 column to the right ?
            .Range("B" & n).Value = Application.VLookup(.Range("A" & n).Value, Search_range, 2, False)
        Next n
    End With
End Sub