时间戳

首页 > 解决方案 > 试图在 VBA 中填充 VLOOKUP，但lookp 仅适用于 2 个单元格

excel - 试图在 VBA 中填充 VLOOKUP，但lookp 仅适用于 2 个单元格

问题描述

我正在尝试编写一个 VBA 代码来帮助我从 C 驱动器上的另一个位置查找值，但显然只有前两个单元格有效。

你能帮我更正这段代码吗？对 VBA 来说真的很新，只是用 Do While Loops 尝试我的水域。

Sub Copy3()

    Dim lookfor As Range
    Dim table_array As Range
    Dim table_array_col As Integer
    Dim lookFor_col As Integer
    Dim Wbk As Workbook
    Dim Rows As Integer

    Rows = 2
    Do While Rows < 60

    Set lookfor = Cells(Rows, 1)

    Set Wbk = Workbooks.Open("C:\Users\XXX.xlsx")
    Set table_array = Wbk.Sheets("Sheet1").Range("B2:H60")
    table_array_col = 5

    lookFor_col = ThisWorkbook.Sheets("Sheet1").UsedRange.Columns.Count - 2
    ThisWorkbook.Sheets("Sheet1").Cells(Rows, lookFor_col).Formula = Application.VLookup(lookfor.Value, table_array, table_array_col, 0)

    Rows = Rows + 1
    Loop
End Sub

标签： excelvbaloops

解决方案

我想我发现了错误。Set lookfor = Cells(Rows, 1)缺少限定符，并且可能没有引用正确的工作簿。Workbooks.Open更改活动工作簿。当 Active Workbook 在执行过程中发生变化时，不合格的范围很容易导致错误。

ThisWorkbook.Sheets("Sheet1")我通过添加对我注意到的其他一些小问题的引用来纠正这一点。值得注意的是，行是 Excel VBA 中内置的范围对象之一。尽可能避免使用已定义的对象名称作为变量名称。

Sub Copy3()
    
    Const table_array_col As Integer = 5
    
    Dim lookfor As Range
    Dim Row As Integer
    
    Dim lookFor_col As Integer
    lookFor_col = Sh.UsedRange.Columns.Count - 2
    
    Dim Wbk As Workbook
    Set Wbk = Workbooks.Open("C:\Users\XXX.xlsx")
    
    Dim table_array As Range
    Set table_array = Wbk.Sheets("Sheet1").Range("B2:H60")
    
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Sheets("Sheet1")
    
    For Row = 2 To 59

        Set lookfor = Sh.Cells(Row, 1)
        
        Sh.Cells(Row, lookFor_col).Value = Application.VLookup(lookfor.Value, table_array, table_array_col, 0)
    
    Next Row
End Sub

推荐阅读