CODEWITHSUNDEEP
excelvba
Fred Blair
Fred Blair

Reputation: 67

VBA Vlookup Macro

I am trying to use a Vlookup macro where the the lookup value is located in cell C18 in a closed workbook. I tried to creating this code shown below, but all I receive is a message stating:

Compile error:

Expected end of statement

Can you please assist?

Sub Vlookup()

Dim myRng
Set myRng = Range("Ledger_Account_2")
Range("F15") = "=VLookup('C:\Users\fbdXXXX\Documents\[PRE500KPC90 - BBB Monthly Reclass - 042023.xlsm]Journal Entry'!C18,Range("MyRng,")2,0)

End Sub

Upvotes: 2

Views: 76

Answers (1)

VBasic2008
VBasic2008

Reputation: 54797

Writing a VLOOKUP Formula

  • Note that this will only work if the source file is closed.
Sub Vlookup()

    Const SRC_FOLDER As String = "C:\Users\fbdXXXX\Documents\"
    Const SRC_FILE As String = "PRE500KPC90 - BBB Monthly Reclass - 042023.xlsm"
    Const SRC_SHEET As String = "Journal Entry"
    Const SRC_CELL As String = "C18"

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
            
    Dim myRng As Range: Set myRng = ws.Range("Ledger_Account_2")
    
    Dim Formula As String: Formula = "=VLOOKUP('" _
        & SRC_FOLDER & "[" & SRC_FILE & "]" & SRC_SHEET _
        & "'!" & SRC_CELL & "," & myRng.Address & ",2,0)"
    
    'Debug.Print Formula
    
    ws.Range("F15").Formula = Formula

End Sub

Upvotes: 2

Related Questions