Custom VBA VLookup Formula

[jOSEFFB]

I'm having a hard time getting a custom vlookup formula to work.

I need to be able to change a part of the range in the vlookup dynamically based on a cell value.

This is what I came up with but it gives me a value error. Can anyone help?

Function VL(val As Variant, RNG As Range, col As Integer, BL As Boolean)
Dim Lookup As Range
Set Lookup = Range("'C:\AirlineData\Total O&D\[" & RNG & " Total O&D.xls]by market'!$B$1:$D$65536")
VL = WorksheetFunction.VLookup(val, Lookup, col, BL)
End Function

 

[jOSEFFB]

This should work but it's not. I'm using excel 2007 and also my breakpoints dont seem to want to work in my ide

Function VL(val As Variant, RNG As String, col As Integer)
MsgBox RNG
Dim stringCombo As String
stringCombo = "'C:\AirlineData\Total O&D\[" & RNG & " Total O&D.xls]by market'!$B$1:$D$65536"
MsgBox stringCombo
VL = Application.WorksheetFunction.VLookup(val, stringCombo, col, 0)
End Function


All the msg boxs come up with the right data

 

[RoryA]

That won't work unless the other workbook is open and you remove the path and refer to a range object. You can't pass a string to the second argument of WorksheetFunction.Vlookup - it has to be a range object.

 

[jOSEFFB]

Is there a way I can modify that cell's formula and refresh it with the new vlookup formula?