Automated tool for VLOOK UP

Question

I need to perform many VLookup in a set of excel files, and for that I built a tool that can make automated VLookup from a file to another but I need to be make it scalable and adaptable.

For that, I want to input in some cells of the tool (which is an Excel file) the parameters for the VLookup:

• Position of Key Column
• Position of "Returned Value" Column
• Number of Columns in the range

Do you know how to change my tool in order for it to include these entry parameters ?

a sample of the code here:

For myrow = 3 To lastrow
    Range("b" & myrow).FormulaR1C1 = _
      "=VLOOKUP(RC[-1], Input!C[-1]:C[2],2,FALSE)"
Next myrow

Answer 1

I'm not well versed in R1C1 notation, but if you were using regular .Formula notation:

The following code assumes that A1 = Key column, A2 = Returned value column, A3 = Number of columns in the range (which is really just your return column).

lastrow = 10 just for the example

Also note - you must be missing a field... since you should have 4 variables - key column for first parameter, 2 column letters for second parameter, and the number of columns for 3rd parameter.

Sub Test()

lastrow = 10

For myrow = 3 To lastrow

    'Range("B" & myrow).Formula = "=VLOOKUP(" & Range("A1").Value & myrow & ",Input!$" & Range("A1").Value & ":$" & Range("A2").Value & "," & Range("A3").Value & ",FALSE)"
    Debug.Print "=VLOOKUP(" & Range("A1").Value & myrow & ",Input!$" & Range("A1").Value & ":$" & Range("A2").Value & "," & Range("A3").Value & ",FALSE)"

Next myrow

End Sub

Values on ActiveSheet:

Immediate window returns: