Excel VBA - Vlookup

Question

I have two worksheets (sheet1 and sheet2). Both contain a column with header "ID" (columns are not always in the same position so need to be found).

Needed is a vlookup in a new column before the "ID" column.

This is what I got so far

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

Everything is working fine until the "vlookup-part" I managed to put a formula in the correct cell, but I just can't get the formula to work.

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?

It would be great if anybody can help me out with the correct vlookup formula / variables and the autofilling.

99moorem 99moorem · Accepted Answer · 2015-07-07T08:50:03

You could also use something similar to below should you want to avoid using the worksheet

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

Valuse/variables will need to be changed of course. lookup_value,Array (range), Column number, Exact match.

Exact match needs false and similar match needs true

Try below full code

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

Excel VBA - Vlookup

2 Answers