VBA vlookup formula error

Question

I am a newbie in excel macro vba. I have a problem on my vlookup code which refers to another workbook selected by a user.

Here's my code:

Private Sub vlookups()

Dim data_file_new As String
Dim i As Integer
Dim a As String, b As String, path As String

data_file_new = CStr(Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Select new data file for VLOOKUP"))

path = data_file_new

a = "=VLOOKUP(A:A,'[" & path & "]Source'!$A:$AB,28,0)"
b = "=VLOOKUP(A:A,'[" & path & "]Source'!$A:$AJ,36,0)"
i = 7

Do Until Sheets("Macro Template").Cells(i, 1) = ""

    Sheets("Macro Template").Cells(i, 37) = a
    Sheets("Macro Template").Cells(i, 38) = b

    i = i + 1
Loop

End Sub

My problem is that my code doesn't give the correct formula for the vlookup. instead, it gives this formula:

=VLOOKUP(A:A,'[E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source]No Approval Monitoring Log_June'!$A:$AB,28,0)

the correct formula is this:

=VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0)

Any help would be appreciated.

Thanks!

Have you had a look at what value is actually in path? Try putting a breakpoint on the line where you assign a value to path and see what value of it is. Sounds like it has the incorrect value in it — Martin
it has the path of the selected file, including the filename. But i don't know why it displays the wrong formula — John F
Firstly "=VLOOKUP(A:A,'[" should be "=VLOOKUP(A:A,'" Secondly. I am wondering how are you getting the first formula. Your first formula should be ending in ]Source'!$A:$AB,28,0) Can you recheck that for me? — Siddharth Rout

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-06-13T10:36:17

Try this (Untested)

Private Sub vlookups()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim sPath As String, sFile As String, sTemp As String
    Dim Ret

    Set ws = ThisWorkbook.Sheets("Macro Template")

    Ret = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
                                      Title:="Select new data file for VLOOKUP")

    If Ret = False Then Exit Sub

    sFile = GetFilenameFromPath(Ret)
    sPath = Split(Ret, sFile)(0)
    sTemp = "=VLOOKUP(A:A,'" & sPath & "[" & sFile

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("AK7:AK" & lRow).Formula = sTemp & "]Source'!$A:$AB,28,0)"
        .Range("AL7:AL" & lRow).Formula = sTemp & "]Source'!$A:$AJ,36,0)"
    End With
End Sub

Public Function GetFilenameFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = _
        GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Explanation:

Application.GetOpenFilename() returns a Variant. Handle it as shown in the code above.
The formula that you are looking for is =VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0) and Ret will give you a straight File path and name E:\AP NO APPROVAL\No Approval Monitoring Log_June 2015 xlsx.xlsx. Vlookup puts a [] around the file name. You have to first extract the filename from the file path and reconstruct the entire string. We use the Function GetFilenameFromPath in the above code to retrieve that.
You don't need to loop cells to enter the formula. You can enter the formula in ONE GO in the entire range.

VBA vlookup formula error

2 Answers