VBA : How do I insert VLookup formula (instead of the values) in a cell?

Question

I am setting a "database" (that mixes Line of Businesses, Business Units and some other informations) of keys in an excel workbook . I need to do a VLookup with these keys and search these keys in another excel workbook. and I want to do this via VBA.

Sub CreateVLookUp()
    Dim rw As Long, x As Range
    Dim extwbk As Workbook, twb As Workbook
    Dim KeyRange As Range
    Dim KeyCell As Range
    Dim lastrow As Long

    lastrow = Worksheets("Parameters").Range("I" & Rows.Count).End(xlUp).Row
    Set KeyRange = Worksheets("Parameters").Range("I8:I" & lastrow)

    Set twb = ThisWorkbook
    Set extwbk = Workbooks.Open("C:\Users\vih8452\Documents\ExcelFile.xlsx")
    Set x = extwbk.Worksheets("Database Structure").Range("$A:$T")



    With twb.Sheets("Parameters")

        For rw = 15 To .Cells(Rows.Count, 10).End(xlUp).Row
            .Cells(rw, 14) = Application.VLookup(.Cells(rw, 9).Value2, x, 15, False)
            .Cells(rw, 15) = Application.VLookup(.Cells(rw, 9).Value2, x, 11, False)
            .Cells(rw, 16) = Application.VLookup(.Cells(rw, 9).Value2, x, 12, False)
        Next rw

    End With

    extwbk.Close savechanges:=False
End Sub

I have already almost succeeded in my task, but the main problem is that when I run the VBA macro, it just pastes the result. What I want is to see the result in the cell, but also the formula in the formula bar (so I can check whether my result is true or false).

Scott Craner · Accepted Answer · 2019-05-10T14:10:57.117

2

You do not need the loop when setting the formula:

Sub CreateVLookUp()
    Dim x As Range
    Dim extwbk As Workbook
    Dim lastrow As Long

    Set extwbk = Workbooks.Open("C:\Users\vih8452\Documents\ExcelFile.xlsx")
    Set x = extwbk.Worksheets("Database Structure").Range("$A:$T")

    With ThisWorkbook.Sheets("Parameters")

        lastrow = .Range("I" & Rows.Count).End(xlUp).Row
        .Range(.Cells(15, 14), .Cells(lastrow, 14)).Formula = "=VLOOKUP(I15," & x.Address(1, 1, xlA1, 1) & ",15,FALSE)"
        .Range(.Cells(15, 15), .Cells(lastrow, 15)).Formula = "=VLOOKUP(I15," & x.Address(1, 1, xlA1, 1) & ",11,FALSE)"
        .Range(.Cells(15, 16), .Cells(lastrow, 16)).Formula = "=VLOOKUP(I15," & x.Address(1, 1, xlA1, 1) & ",12,FALSE)"

    End With
    extwbk.Close savechanges:=False
End Sub

edited May 10 '19 at 14:10

answered May 10 '19 at 14:02

Scott Craner

148,073
10
49
81

Perfect, it is finally working. Thanks a lot for your help – Orfuz May 10 '19 at 14:15
A last question to fully understand how this vba code function. How does the VLookup knows in the row 16 by example, it switches the lookup value to I16 and so on ? – Orfuz May 10 '19 at 16:49
@Orfuz Because internally the formulas are in R1C1 like the [other answer](https://stackoverflow.com/a/56079204/11683) shows, and in R1C1 all cells in your column have [identical formulas](https://stackoverflow.com/a/55532259/11683). `vlookup` does not do anything to make it happen. – GSerg May 10 '19 at 18:11
@Orfuz it acts like a drag fill, as it goes down the column it will change any relative references accordingly. – Scott Craner May 10 '19 at 18:14

Pspl · Answer 2 · 2019-05-10T14:05:06.983

1

You have to write like this:

 .Cells(rw, 14).FormulaR1C1 = "=VLOOKUP(" & .Cells(rw, 9).Value & ", C1:C20, 15, FALSE)"

or

 .Cells(rw, 14).FormulaR1C1 = "=VLOOKUP(RC[-5], C1:C20, 15, FALSE)"

To refer another sheet just place:

 .Cells(rw, 14).FormulaR1C1 = "=VLOOKUP('Sheet Name'!RC[-5], 'Sheet Name'!C1:C20, 15, FALSE)"

edited May 10 '19 at 14:05

answered May 10 '19 at 13:59

Pspl

1,398
12
23

one problem, the lookup range is in a different workbook and sheet. This will lookup the range on the wrong sheet. – Scott Craner May 10 '19 at 14:03
I am getting a Run-time error '1004': when trying to run your code Pspl And yes Scott is right, this will not lookup in the right range as the range is in another workbook. – Orfuz May 10 '19 at 14:05
Finally, show that `rw` and the loop are not needed. – GSerg May 10 '19 at 14:06
Still, with the code : .Cells(rw, 14).FormulaR1C1 = "=VLOOKUP('Sheet Name'!RC[-5], 'Sheet Name'!C1:C20, 15, FALSE)" The problem is that I can´t refer to the sheet in the other workbook – Orfuz May 10 '19 at 14:10
@Orfutz, I don't get why you're getting an error. In any way it has nothing to do with the formula. If it has an error is not a run-time. It's a formula error and display's it on the cell. – Pspl May 10 '19 at 14:15

VBA : How do I insert VLookup formula (instead of the values) in a cell?

2 Answers2