vba - Excel VBA - Vlookup

Question

我有两个工作表(sheet1 和 sheet2)。两者都包含一个带有标题“ID”的列(列并不总是在同一位置，因此需要找到)。

需要在“ID”列之前的新列中进行 vlookup。

这是我到目前为止得到的

sub vlookup ()

    FIND COLUMNS WITH "ID"-HEADER

        'Set variables for Column Sku
        'note: cfind1 is for sheet 1 and cfind 2 is for sheet 2

            Dim col As String, cfind1 As Range, cfind2 As Range

            column = "ID"

            Worksheets(1).Activate
            Set cfind1 = Cells.Find(what:=column, lookat:=xlWhole)

            Worksheets(2).Activate
            Set cfind2 = Cells.Find(what:=column, lookat:=xlWhole)


    'CREATE COLUMN WITH VLOOKUP

            'activate worksheet 1
            Worksheets(1).Activate

            'add column before sku-column
            cfind1.EntireColumn.Insert

            'Select cell 1 down and 1 to left of sku-cell.
            cfind1.Offset(1, -1).Select

            'Add VlookUp formulas in active cell
            ActiveCell.Formula = "=VLOOKUP(LookUpValue, TableArray,1,0)"

                '(Lookup_Value should refer to one cell to the right 
                 (= cfind1.Offset (1, 0)??)

                'Table_Array should refer to the column in sheet(2) with header "id"


             'Autofill Formula in entire column
                 '???
End Sub

一切正常，直到“vlookup-part”
我设法将公式放在正确的单元格中，但我无法让公式起作用。

如何在同一张表中将lookup_value设置为“右侧一个单元格”
和“table_array”作为工作表(2)中标题为“ID”的列？

以及如何最终在整个列中自动填充 vlookup 公式？

如果有人可以帮助我使用正确的 vlookup 公式/变量和自动填充，那就太好了。

Answer 1

如果您想避免使用工作表，您也可以使用类似于下面的内容

curr_stn = Application.WorksheetFunction.VLookup(curr_ref, Sheets("Word_Specifications").Range("N:O"), 2, False)

当然，值/变量需要更改。查找值，数组(范围)，列号，完全匹配。

完全匹配需要 false，相似匹配需要 true

试试下面的完整代码

Sub t()

Dim col As String, cfind1 As Range, cfind2 As Range

            Column = "ID"

            Worksheets(1).Activate
            Set cfind1 = Cells.Find(what:=Column, lookat:=xlWhole)

            Worksheets(2).Activate
            Set cfind2 = Cells.Find(what:=Column, lookat:=xlWhole)


    'CREATE COLUMN WITH VLOOKUP

            'activate worksheet 1
            Worksheets(1).Activate

            'add column before sku-column
            cfind1.EntireColumn.Insert

            'Select cell 1 down and 1 to left of sku-cell.
            cfind1.Offset(1, -1).Select

            'Add VlookUp formulas in active cell
            LookUp_Value = cfind1.Offset(1, 0).Address(False, False)
            Table_Array = Col_Letter(Worksheets(2).Cells.Find(what:=Column, lookat:=xlWhole).Column) & ":" & Col_Letter(Worksheets(2).Cells.Find(what:=Column, lookat:=xlWhole).Column)
            ws_name = Worksheets(2).Name
            Col_index_num = 1
            Range_Lookup = False
            ActiveCell.Formula = "=VLOOKUP(" & LookUp_Value & ", " & ws_name & "!" & Table_Array & ", " & Col_index_num & ", " & Range_Lookup & ")"

            'Autofill Formula in entire column
            lastrow = Range(cfind1.Address).End(xlDown).Row
            Range(cfind1.Offset(1, -1).Address).AutoFill Destination:=Range(cfind1.Offset(1, -1).Address & ":" & Col_Letter(cfind1.Offset(1, -1).Column) & lastrow), Type:=xlFillDefault


End Sub


Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function