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!

Answer 1

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:

1. Application.GetOpenFilename() returns a Variant. Handle it as shown in the code above.

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

3. You don't need to loop cells to enter the formula. You can enter the formula in ONE GO in the entire range.

Answer 2

You are not specifying the sheet your formula is referring to. That is the problem.