VLOOKUP - with File Name as a Variable (not working)

Your Misc2 works out as;

Please Login or Register  to view this content.

You've not defined Lookup1 or Range1 in your parameters in the post but I'm guessing this is an oversight in your Post?

Perhaps if you posted your actual code then we'd get a better idea of what might be going wrong?

Hello PaulSp8,

You are right - in my urgency - I missed out a few parameters (and to provide Values to them)

Here is the function:

**

Function GetVal1() As Variant

Dim Date1, sPath, SFile, sheetname1, Misc1, Misc2, lookup1, range1 As String

Date1 = Format(Now() - 1, "YYYYMMDD")
sPath = "\\Network-One\Collections\Summary\"
SFile = "SUMMARY_" & Date1 & ".xlsx"
sheetname1 = "Report"
Misc1 = "VLOOKUP("
lookup1 = """BALANCE"""
range1 = "$A$4:$H$40"

'Concatenate all of the above (to arrive at a VLOOKUP Statement - similar to the one that ran successfully (as posted in my Original request)

Misc2 = Misc1 & lookup1 & "," & "'" & sPath & "[" & SFile & "]" & sheetname1 & "'!" & range1 & ",8, False)"
MsgBox Misc2

MsgBox ExecuteExcel4Macro(Misc2)

End Function

**

Thanks