Excel VBA - Vlookup

我有两个工作表(sheet1和sheet2) . 两者都包含一个 Headers 为“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”我设法将公式放入正确的单元格中,但我无法让公式起作用 .

How can I set lookup_value as "one cell to the right" in the same sheet and "table_array" as the column with header "ID" in worksheet(2)?

And how can I finally autofill the vlookup formula throughout the whole column?

如果任何人可以帮助我使用正确的vlookup公式/变量和自动填充,那将是很好的 .

回答(2)

3 years ago

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

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

当然需要更改值/变量 . lookup_value,数组(范围),列号,完全匹配 .

完全匹配需要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

3 years ago

之前没有这样做,但我的方法是使用cell或range.formula属性并构建您将在单元格中写入的字符串 . 例如:

myrange.formula = "=Vlookup("&Lookup_Value&","&Table_Array&","&Col_index_num&","&Range_Lookup&")"