VBA vlookup formula error

Try this ( Not Indexed )

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
  
  . Treat it as shown in the above code.

• The formula you are looking for, =VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0)
  
  and Ret
  
  give you a straight path and file name E:\AP NO APPROVAL\No Approval Monitoring Log_June 2015 xlsx.xlsx
  
  . Vlookup
  
  puts []
  
  around the filename. You must first extract the filename from the file path and restore the entire line. We are using Function GetFilenameFromPath
  
  the above code to get this.

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