How to use dynamic Match formula inside a vlookup in VBA?

user_10883763 0
Viewed 593

I need a code in VBA which is equivalent to match inside a vlookup in Excel. Have got 2 sheets one MasterSheet and another required_data_sheet.

There are 18 columns in MasterSheet and in required_data_sheet there are only 11 out of 18 columns of MasterSheet (not all of these 11 columns are in same order as first 11 columns in MasterSheet). The first column in both sheets is item_id which is the primary key. MasterSheet has 45000 records and Required_data_sheet has only few hundreds rows with only 1st column (which is item ID) populated and for these few hundred other 10 (11-1st column) needs to be populated.

I can use below formula in required_data_sheet to get data from mastersheet, which gives correct result. I only write this formula in cell A2 of required_data_sheet and then copy the same in all cells.

=VLOOKUP($A2,Master,MATCH(B$1,Master[#Headers],0),FALSE)

Master is the table name of data in MasterSheet.

Problem is I am unable to write the correct VBA code for this. Code is problematic in the Match formula part.

Below is my code and Match formula is preventing it from giving the desired result.

Sub Fetch_Specific_Columns()
    Dim lastrow As Long
    Dim lastcolumn As Integer
    Dim c As Integer
    Dim r As Long

    lastrow = Range("A" & Rows.Count).End(xlUp).row
    lastcolumn = Range("A1").End(xlToRight).Column

    For c = 2 To lastcolumn
        For r = 2 To lastrow
            Worksheets("Required_Data_Sheet").Cells(r, c).Formula = "=VLOOKUP(A" & r & ",Master,MATCH(" & Worksheets("Required_Data_Sheet").Cells(1, c) & ",'Master Sheet'!$A$1:$R$1,0),FALSE)"
        Next r        
    Next c
End Sub

Expected result is somehow match formula works in the desired fashion.

enter image description here

vba excel
1 Answers

The problem with your code is this portion:

MATCH(" & Worksheets("Required_Data_Sheet").Cells(1, c) & ",

It is inserting the value from cell(1,c) directly into the formula without quotes. This is incorrect excel formula syntax. Instead you should either:

  1. Insert a cell reference (e.g. B3) or
  2. The value of the cell in quotation marks.

Here is the whole line with the correction for method 1:

Worksheets("Required_Data_Sheet").Cells(r, c).Formula = "=VLOOKUP(A" & r & ",Master,MATCH(" & Worksheets("Required_Data_Sheet").Cells(1, c).Address & ",'Master Sheet'!$A$1:$R$1,0),FALSE)"

Here is the whole line with the correction for method 2:

Worksheets("Required_Data_Sheet").Cells(r, c).Formula = "=VLOOKUP(A" & r & ",Master,MATCH(""" & Worksheets("Required_Data_Sheet").Cells(1, c) & """,'Master Sheet'!$A$1:$R$1,0),FALSE)"

user_51289610
Related