CODEWITHSUNDEEP
excelvba
Orfuz
Orfuz

Reputation: 3

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

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).

Upvotes: 0

Views: 1632

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

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

Upvotes: 2

Pspl
Pspl

Reputation: 1474

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)"

Upvotes: 1

Related Questions