首页 文章

Excel VBA - Vlookup

提问于
浏览
2

我有两个工作表(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 回答

  • 0

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

    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
    
  • 0

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

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

相关问题