Search code examples
excelvba

Vlookup Function Excel VBA

I have 2 worksheets. The first one is for the working file and the second is for the master file sheet. The master file sheet has 3 columns which are as follows Fund Number, Name, Portfolio Number. In the working file sheet, specifically in column 2, it has the values in the Fund Number Column of the master file sheet. What I would like to do is to perform vlook up function in the working file sheet column 2, that will get the corresponding values in Portfolio Number Column in the master file sheet and autofill it till the last row. Is vlookup function appropriate for this matter?

Any help will be highly appreciated.

Sub VLOOKUP_Formula_1()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Working File")

    Dim lr As Integer
    lr = sh.Range("B" & Application.Rows.Count).End(xlUp).Row

     sh.Range("B2").Value = "=VLOOKUP(B2,Database!A:C,3,0)"

    sh.Range("B2" & lr).FillDown
    sh.Range("B2" & lr).Copy
    sh.Range("B2" & lr).PasteSpecial xlPasteValues

Application.CutCopyMode = False

End Sub
Solution

  • A VBA Lookup (VLookup Formula)

    enter image description here

    Sub LookupPortfolio()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Working File")
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If LastRow < 2 Then Exit Sub ' no data
    
    With ws.Range("C2:C" & LastRow) ' adjust the return column!
        .Formula = "=VLOOKUP(B2,Database!A:C,3,0)"
        .Value = .Value
    End With
    
    MsgBox "Portfolio looked up.", vbInformation
    
End Sub